December 12, 2011 at 6:38 pm
I am having problem in generating the table description for a Single Database. I am trying to generate ALL the tables, their columns and the column description. I get the result including the column description. But, I am not able to see the columns that doesn't have the description. I am expecting an output of the table description column to ALSO produce NULL or empty value including the description column.
Columns: Table Name - Column Name - TypeName- Is_Nullable - Is_Identity-Table Description
It should basically produce ALL the database related tables and their columns and the column description.
I tried the following query, it works fine BUT it is NOT returning any table columns which are not defined.i.e it should ALSO return either NULL or empty on the column which doesn't have any table description in it. Please, do some help. Thank you.
SELECT t.name As Tablename,
c.name AS ColumnName,
ty.name AS TypeName,
c.is_nullable As Is_Nullable,
c.is_identity As Is_Identity,
ep.Value As TableDescription
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
INNER JOIN sys.types as ty ON c.user_type_id = ty.user_type_id
December 12, 2011 at 7:21 pm
Try this:
SELECT t.name As Tablename,
c.name AS ColumnName,
ty.name AS TypeName,
c.is_nullable As Is_Nullable,
c.is_identity As Is_Identity,
ep.Value As TableDescription
FROM sys.tables AS t
LEFT OUTER JOIN sys.extended_properties AS ep ON t.object_id = ep.major_id
LEFT OUTER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
LEFT OUTER JOIN sys.types as ty ON c.user_type_id = ty.user_type_id
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 12, 2011 at 7:57 pm
Hi Jason,
Thank you for your response. I tried with Adventureworks2008R2 DB. Interestingly, when I run the query which you have mentioned, For some of the tablenames I am noticing some of the column name, typename, Is_nullabe, is_identity as NULL. Is this expected? If so can you provide reason please. I am expecting the following ouput
ex: Tablename column name TypeName Is_Nullabe Is_Identity Table Description
T1 name char 1 0 Employee Name
T1 ID int 1 0 NULL
I am expecting the output like this. It should provide description for the column which is available and should provide NULL/EMPTY when description is not available.
December 12, 2011 at 8:32 pm
Not all tables have extended properties. The way you constructed the query to rely on major version and minor version requires extended properties to be declared prior to the last two views. If there is no extended_property and based on this setup, this is the expected behavior.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 12, 2011 at 9:26 pm
Thank you so much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply