Hey buddy, kind souls? 😀

Fine here is how u can do it capture the mail

Declare @MailItemID INT

EXEC @RetCode = msdb.dbo.sp_send_dbmail

...

Shilpa, this is such a biiiiiiiiggggg requirement i would say...:-D

but nonetheless not hard.. i had come up with a piece of code that might work for u.. kindly inform us...

removed the code as i dint get the requirement clearly..

You can also Information_Schema.Column View to get information about all the columns in a database..

sample query

SELECT

TABLE_NAME

,COLUMN_NAME

,DATA_TYPE

,CHARACTER_MAXIMUM_LENGTH

,NUMERIC_PRECISION

,NUMERIC_SCALE

...

CirquedeSQLeil (4/20/2010)

---

COldCoffee (4/20/2010)

---

Jason, Il est la...

Jason, Il est la forme abrégée...

Jeff, you really are a wonderful character.. and for paul and karthik, thanks for providing new views and test results.. u guys are simply great.. thanks all 🙂

Paul, i initially coded both pivot-join and nested-pivot solution which is exactly similar to yours, but i feared tat 2 pivots and a join willl over-load the cpu and i...

Jeff Moden (4/19/2010)

---

COldCoffee (4/19/2010)

---

Yes Karthik, i could also see a slight change in the execution plan, but that extra component dint cost the query.. so i guess the query performs...

Yes Karthik, i could also see a slight change in the execution plan, but that extra component dint cost the query.. so i guess the query performs equally with Jeff's,...

lleemon13 (4/19/2010)

---

That seems to be the trick.

Here is the code with no errors. Should I still add the ISNULL check? I am not sure how you do...

pYak (4/19/2010)

---

Thanks COld

I love seeing how the same problem can be approached from different angles.

To tell the truth I tried to use the pivot operator with the data set

but couldn't...

Thats the problem when the requirement is unclear with no DDL...

Also i got a doubt here; Are there any values in the p_code, p_name columns that dont comply to the Rules of Identifiers?? [For rules of identifiers CLICK HERE...

Had u posted the necessary DDL, sample data and desired output, people would have replied and fixed your problem just at the wink of your eyes..:-)

Anyways, happy to have helped...