January 11, 2011 at 8:24 pm
Comments posted to this topic are about the item SQL SERVER 2008 Data Dictionary
January 12, 2011 at 5:59 am
You get a 5 star rate from me.. This was just what I was looking for. We just made our database 100% documented, which on the buildserver for deploy builds will generate database documentation (using redgate sql doc).
Now I can use parts of this script to trow an error/warnings when there are new undocumented columns or tables..
However, the primary key check is broken. If I have time today I will take a look at that and post back here.
January 12, 2011 at 8:50 am
This is the way I've done the PK determination:
Join in sysobjects:
FROM sys.columns AS c
INNER JOIN sysobjects so
ON c.object_id = so.id
Then use this to display the PK flag:
CASE WHEN c.name IN (
SELECT c.name
FROM sysindexes i
JOIN sysobjects o
ON i.id = o.id
JOIN sysobjects pk
ON i.name = pk.name
AND pk.parent_obj = i.id
AND pk.xtype = 'PK'
JOIN sysindexkeys ik
ON i.id = ik.id
AND i.indid = ik.indid
JOIN syscolumns c
ON ik.id = c.id
AND ik.colid = c.colid
WHERE o.name = so.name) THEN 'Y'
ELSE ''
END AS 'PK'
January 12, 2011 at 9:52 am
Ok, this is showing the Primary key columns correctly:
SELECT DISTINCT
t.name AS Table_Nme, ept.value AS Table_Desc, c.name AS Column_Nme,
st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type,
CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind, epc.value AS Column_Desc,
CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value,
CASE WHEN PrimaryKeyColumns.CONSTRAINT_NAME IS NOT null THEN 'Yes' ELSE '' END AS Primary_Key_Ind,
CASE WHEN t .object_id = fk.parent_object_id AND
c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind, CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind,
ft.name AS Foreign_Table, c.column_id
FROM sys.columns AS c INNER JOIN
sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN
sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN
sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN
sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN
sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN
sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN
sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN
sys.tables AS ft ON fk.referenced_object_id = ft.OBJECT_ID
--start added by hjm
LEFT OUTER JOIN (
SELECT
Tab.TABLE_NAME,
Tab.CONSTRAINT_NAME,
K.COLUMN_NAME,
K.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON Tab.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE
Tab.CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS PrimaryKeyColumns ON (PrimaryKeyColumns.COLUMN_NAME = c.name AND t.NAME = PrimaryKeyColumns.TABLE_NAME )
--hjm end
WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')
ORDER BY Table_Nme, c.column_id
I think we can make the rest also a bit more readable by using schema information views, but hey. Its working and I really like the integration of the extended properties...
Cheers,
HJ Meulekamp
January 12, 2011 at 11:46 am
Nice Job everyone.
I have been working on creating a DD for a new app for a few days (vendor could not provide).
The last blog code with the sub-query is okay, but I have to mention that in our app's db_name.schema.table two elements indicated as PK.
This may not occur with every DB, but I will find out why and post.
Kudos to those who extend their talent.
February 1, 2011 at 2:03 pm
I put one together sometime ago but this is by far much better then what I had pieced together, matter of fact this puts mine to shame.
Thank you for sharing.
-- Samson
January 22, 2014 at 8:31 am
Here is a modified version that shows the primary and foreign key names and includes another way to correct the primary key issue the original one had.
SELECT DISTINCT
t.name AS Table_Nme,
ept.value AS Table_Desc,
c.name AS Column_Nme,
st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type,
c.column_id,
CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind,
epc.value AS Column_Desc,
CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value,
CASE WHEN (select count(*) from sys.key_constraints where type_desc = 'PRIMARY_KEY_CONSTRAINT' and parent_object_id = t.object_id) = 1 THEN 'Yes' ELSE 'No' END AS Primary_Key_Ind,
CASE WHEN t .object_id = fk.parent_object_id AND
c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind,
CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind,
ft.name AS Foreign_Table,
(SELECT name FROM sys.key_constraints where type_desc = 'PRIMARY_KEY_CONSTRAINT' and parent_object_id = t.object_id) AS PK_Name,
object_name(fk.constraint_object_id) AS Foreign_Key_Name
FROM sys.columns AS c INNER JOIN
sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN
sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN
sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN
sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN
sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN
sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN
sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN
sys.tables AS ft ON fk.referenced_object_id = ft.object_id
WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')
ORDER BY Table_Nme, c.column_id;
Cheers
January 22, 2014 at 9:52 am
The last script posted shows the Primary_Key_Ind = Yes for every column in my database.
That's definitely not correct!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 22, 2014 at 10:45 am
Try this one instead.
SELECT DISTINCT
t.name AS Table_Nme,
ept.value AS Table_Desc,
c.name AS Column_Nme,
st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type,
c.column_id,
CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind,
epc.value AS Column_Desc,
CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value,
CASE WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME AND b.constraint_type = 'PRIMARY KEY' AND a.TABLE_NAME = t.name AND a.COLUMN_NAME = c.name) = 1 THEN 'Yes' ELSE 'No' END AS Primary_Key_Ind,
CASE WHEN t .object_id = fk.parent_object_id AND
c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind,
CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind,
ft.name AS Foreign_Table,
(SELECT TOP 1 a.constraint_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME
AND b.constraint_type = 'PRIMARY KEY'
AND a.COLUMN_NAME = c.NAME
AND a.TABLE_NAME = object_name(c.object_id) AND a.ordinal_position = c.column_id) AS Primary_Key_Name,
object_name(fk.constraint_object_id) AS Foreign_Key_Name
FROM sys.columns AS c INNER JOIN
sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN
sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN
sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN
sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN
sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN
sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN
sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN
sys.tables AS ft ON fk.referenced_object_id = ft.object_id
WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')
AND (select count(*) from sys.key_constraints where type_desc = 'PRIMARY_KEY_CONSTRAINT' and parent_object_id = t.object_id) = 1
ORDER BY Table_Nme, c.column_id;
Cheers
January 22, 2014 at 11:36 am
The above version seems to work.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 22, 2014 at 1:05 pm
Here's an improved version that includes the Schema and PKs and FKs; Just add the DB extended properties to describe field contents and Voilá
SELECT DISTINCT
t.name AS Table_Name, sc.name as [schema],
ept.value AS Table_Desc,
c.name AS Column_Name,
st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type,
c.column_id,
CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind,
epc.value AS Column_Desc,
CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value,
CASE WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME AND b.constraint_type = 'PRIMARY KEY' AND a.TABLE_NAME = t.name AND a.COLUMN_NAME = c.name) = 1 THEN 'Yes' ELSE 'No' END AS Primary_Key_Ind,
CASE WHEN t .object_id = fk.parent_object_id AND
c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind,
CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind,
ft.name AS Foreign_Table,
(SELECT TOP 1 a.constraint_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME
AND b.constraint_type = 'PRIMARY KEY'
AND a.COLUMN_NAME = c.NAME
AND a.TABLE_NAME = object_name(c.object_id) AND a.ordinal_position = c.column_id) AS Primary_Key_Name,
object_name(fk.constraint_object_id) AS Foreign_Key_Name
FROM sys.columns AS c INNER JOIN
sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN
sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN
sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN
sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN
sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN
sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN
sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN
sys.tables AS ft ON fk.referenced_object_id = ft.object_id
inner join sys.schemas sc on t.schema_id = sc.schema_id --
WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')
AND (select count(*) from sys.key_constraints where type_desc = 'PRIMARY_KEY_CONSTRAINT' and parent_object_id = t.object_id) = 1
ORDER BY Table_Name, c.column_id;
January 23, 2014 at 3:52 pm
[font="Comic Sans MS"]The script given by Mr. Serres applied to my CRM database returns 5923 ROWS.
The original script by Mr. Khan returned 6504 ROWS.
Given the number of rows, it is going to take me some time to determine why the two numbers don't jive.[/font]
January 23, 2014 at 4:10 pm
[font="Comic Sans MS"]Unless I managed to mangle the code while trying to make it more readable, this is the section that causes the difference between the two results sets.
I'll look later as to how these two sections of code do not return the same thing
[/font]
[font="Comic Sans MS"]ORIGINAL CODE[/font]
[font="Courier New"]CASE
WHEN REPLACE
(
REPLACE
(
REPLACE
(
REPLACE
(
REPLACE
(
REPLACE(PK.NAME, 'PK_', '')
, 'PK2_', ''
)
, 'PK3_', ''
)
, 'PK4_', ''
)
, 'PK5_', ''
)
, 'PK1_', ''
)
= c.name THEN 'Yes'
ELSE ''
END AS Primary_Key_Ind,[/font]
[font="Comic Sans MS"]Mr. Serres' code[/font]
[font="Courier New"]CASE
WHEN
(
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a.TABLE_NAME = b.TABLE_NAME
AND b.constraint_type = 'PRIMARY KEY'
AND a.TABLE_NAME = t.name
AND a.COLUMN_NAME = c.name
) = 1 THEN 'Yes'
ELSE 'No'
END AS Primary_Key_Ind, [/font]
April 17, 2014 at 6:49 pm
Edit: After I posted this I realized that I did not try out Jorge Serres' version above, which does in fact contain the schema name.
I noticed that none of the queries here return the table schema as part of the table name even though SQL 2008 supports having tables with the same name as long as they belong to different schemas. Since I have databases that make use of schemas to control access to tables and objects, none of these would work for me. I really like oradbguru's version of the query here and, since I was unable to find any other solution that also included the schema, I decided to try to modify this query instead and share the results.
Here's the modified version, which now displays the table name including the table schema as part of the name.
SELECT DISTINCT sc.NAME + '.' + t.NAME AS Table_Name, ept.value AS Table_Desc, c.NAME AS Column_Name,
st.NAME + '(' + CASE
WHEN c.max_length = - 1
THEN 'max'
ELSE CAST(c.max_length AS VARCHAR(100))
END + ')' AS Column_Data_Type, c.Column_ID, CASE
WHEN c.is_nullable = 0
THEN 'False'
ELSE 'True'
END AS Null_Allowed_Ind, epc.value AS Column_Desc, CASE
WHEN dc.DEFINITION LIKE '(getdate())'
THEN 'Current Date'
ELSE dc.DEFINITION
END AS Column_Default_Value, CASE
WHEN (
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME
AND b.constraint_type = 'PRIMARY KEY' AND a.TABLE_NAME = t.NAME AND a.COLUMN_NAME = c.NAME
) = 1
THEN 'Yes'
ELSE 'No'
END AS Primary_Key_Ind, CASE
WHEN t.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
THEN 'Yes'
ELSE ''
END AS Foreign_Key_Ind, CASE
WHEN c.is_identity = 1
THEN 'Yes'
ELSE ''
END AS Identity_Column_Ind, ft.NAME AS Foreign_Table, (
SELECT TOP (1) a.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS a
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS b ON a.TABLE_NAME = b.TABLE_NAME AND b.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND a.COLUMN_NAME = c.NAME AND a.TABLE_NAME = OBJECT_NAME(c.object_id) AND a.ORDINAL_POSITION = c.column_id
) AS Primary_Key_Name, OBJECT_NAME(fk.constraint_object_id) AS Foreign_Key_Name
FROM sys.columns AS c
INNER JOIN sys.systypes AS st ON st.xtype = c.user_type_id
LEFT OUTER JOIN sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id
LEFT OUTER JOIN sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
RIGHT OUTER JOIN sys.schemas AS sc ON t.schema_id = sc.schema_id
LEFT OUTER JOIN sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id
LEFT OUTER JOIN sys.key_constraints AS pk ON t.object_id = pk.parent_object_id
LEFT OUTER JOIN sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id
LEFT OUTER JOIN sys.tables AS ft ON fk.referenced_object_id = ft.object_id
WHERE (t.NAME NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.NAME NOT LIKE '%sysname%')
ORDER BY Table_Name, c.column_id
April 17, 2014 at 7:47 pm
After testing out the different versions again on some of my databases, I noticed the same discrepancy in the number of records returned between Abdullah Khan's query and Jorge Serres' (and everybody else's) queries. The queries are missing any tables that do not have a defined primary key. If you comment out the next to last line, it shows all the tables.
Here's the query with the commented line:
SELECT DISTINCT
t.name AS Table_Name, sc.name as [schema],
ept.value AS Table_Desc,
c.name AS Column_Name,
st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type,
c.column_id,
CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind,
epc.value AS Column_Desc,
CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value,
CASE WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME AND b.constraint_type = 'PRIMARY KEY' AND a.TABLE_NAME = t.name AND a.COLUMN_NAME = c.name) = 1 THEN 'Yes' ELSE 'No' END AS Primary_Key_Ind,
CASE WHEN t .object_id = fk.parent_object_id AND
c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind,
CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind,
ft.name AS Foreign_Table,
(SELECT TOP 1 a.constraint_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME
AND b.constraint_type = 'PRIMARY KEY'
AND a.COLUMN_NAME = c.NAME
AND a.TABLE_NAME = object_name(c.object_id) AND a.ordinal_position = c.column_id) AS Primary_Key_Name,
object_name(fk.constraint_object_id) AS Foreign_Key_Name
FROM sys.columns AS c INNER JOIN
sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN
sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN
sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN
sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN
sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN
sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN
sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN
sys.tables AS ft ON fk.referenced_object_id = ft.object_id
INNER join sys.schemas sc on t.schema_id = sc.schema_id --
WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')
--AND (select count(*) from sys.key_constraints where type_desc = 'PRIMARY_KEY_CONSTRAINT' and parent_object_id = t.object_id) = 1
ORDER BY Table_Name, c.column_id;
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply