January 25, 2008 at 4:07 pm
How can I find the columns in my tables that do not have an entry in the MS_Description field?
The following code gives me a list of the descriptions associated with all the tables:
SELECT objtype, objname, value
FROM fn_listextendedproperty
('MS_Description', 'schema', 'dbo', 'table', default, NULL, NULL)
And the following code gives me a list of the column descriptions in one particular table (this example, t_Account):
SELECT objtype, objname, value
FROM fn_listextendedproperty
('MS_Description', 'schema', 'dbo', 'table', 't_Account', 'column', default)
However, the only tables/columns that are included in the result set are those with a description. I was hoping to get a result set of all tables/columns, regardless of whether a description existed, and then be able to filter it with 'WHERE value IS NULL'.
Any suggestions as to how can I find the missing descriptions would be most appreciated!
[More info: I'm creating a data dictionary using 'SchemaToDoc', and including the MS_Descriptions associated with the tables and the columns in the document. I'm hoping to find a more efficient way way to identify the ones without descriptions, rather than going through a couple hundred pages of a Word document.]
..Gwen Sembroski..
January 26, 2008 at 4:53 am
fn_listextendedproperty, as you've found, only returns values where those values exist. What you'd need to do is combine the output of that query through a left join with a valid list of columns, either sys.columns or information_schema.columns. Something along the lines of:
select c.column_name
,ex.value
from
information_schema.columns c
left join (SELECT objtype, objname, value
FROM fn_listextendedproperty
('MS_Description', 'schema', 'dbo', 'table', default, NULL, NULL)) as ex
on c.column_name = ex.objname
But, if you have columns with the same name in your database, this won't work. You should probably look at joining between the views mentioned above and the sys.extended_properties catalog view. That way you can match table to table, and column to column using the object id's instead of relying on the names, which may not work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2008 at 8:46 am
Thanks, Grant. I'll see what I can do with this!
..Gwen..
January 28, 2008 at 10:04 am
Running that query gives me an error message I don't understand:
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I first took a look at all of the columns in information_schema and found the collation_name column. Then I selected just those records 'WHERE collation_name IS NOT NULL' and sorted the output by collation_name. The only entries had collation_name = SQL_Latin1_General_CP1_CI_AS.
So, where is the conflict coming from?
My (limited) understanding is that the query wouldn't have worked anyway, since it would have tried to match column names (from information_schema) to table names (from fn_listextendedproperty). So, I next tried changing the query so that it would compare the information_schema to just the specific columns of one table:
SELECT c.column_name, ex.value
FROM information_schema.columns c
LEFT JOIN (SELECT objtype, objname, value
FROM fn_listextendedproperty ('MS_Description','schema','dbo', 'table',
't_Account', 'column', default)) AS ex
ON c.column_name = ex.objname
But I'm still getting that same error.
Any ideas?
January 28, 2008 at 11:01 am
I'm not sure why you're getting the collation errors. One of the columns must be marked differently. How about this as a query:
select p.name
,p.value
,t.name as TableName
,c.name as ColumnName
from sys.tables t
join sys.columns c
on t.object_id = c.object_id
left join sys.extended_properties p
on p.major_id = t.object_id
order by t.name desc
,c.name desc
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2008 at 3:53 pm
This didn't exactly work. All the descriptions are listed for each column. For example, if my t_Account table simply had column names of acct, name, & address, and each column has a description, then my result set looks like:
MS_Description, This is the acct description, t_Account, Acct
MS_Description, This is the name description, t_Account, Acct
MS_Description, This is the address descript, t_Account, Acct
MS_Description, This is the acct description, t_Account, Name
MS_Description, This is the name description, t_Account, Name
MS_Description, This is the address descript, t_Account, Name
MS_Description, This is the acct description, t_Account, Address
MS_Description, This is the name description, t_Account, Address
MS_Description, This is the address descript, t_Account, Address
AND, if my table was missing a description for the Name, the output would look like:
MS_Description, This is the acct description, t_Account, Acct#
MS_Description, This is the address descript, t_Account, Acct#
MS_Description, This is the acct description, t_Account, Name
MS_Description, This is the address descript, t_Account, Name
MS_Description, This is the acct description, t_Account, Address
MS_Description, This is the address descript, t_Account, Address
Any other suggestions?
But on a positive note, I used the basis of the query and modified it to get a list of tables that had no descriptions at all!
SELECT DISTINCT t.name
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
LEFT JOIN sys.extended_properties p on p.major_id = t.object_id
WHERE p.value IS NULL
ORDER BY t.name
I do appreciate the help -- this is quite a learning experience for me!
..Gwen..
January 29, 2008 at 12:49 am
I saw a presentation by the developer of this tool at the Denver SQL Server Users group a couple of weeks ago and it will show you exactly which objects have descriptions, etc.:
In free beta through February if I remember correctly.
Joe
January 29, 2008 at 6:27 am
Yeah, I noticed this morning that I didn't have it quite right. I'd joined against the table, but I didn't also provide criteria for the column. Sorry about that. How's this one?
select p.name
,p.value
,t.name as TableName
,c.name as ColumnName
from sys.tables t
join sys.columns c
on t.object_id = c.object_id
left join sys.extended_properties p
on p.major_id = t.object_id
and p.minor_id = c.column_id
order by t.name desc
,c.name desc
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2008 at 6:54 am
SUCCESS! Thank you SOOOO much!!
Joe: Thanks for the link. I'll look into it.
..Gwen..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply