Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL SERVER 2008 Data Dictionary Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 8:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 21, 2011 5:47 AM
Points: 0, Visits: 73
Comments posted to this topic are about the item SQL SERVER 2008 Data Dictionary
Post #1046235
Posted Wednesday, January 12, 2011 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 9, 2012 12:11 PM
Points: 9, Visits: 52

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.



Post #1046428
Posted Wednesday, January 12, 2011 8:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:27 PM
Points: 109, Visits: 957
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'

Post #1046546
Posted Wednesday, January 12, 2011 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 9, 2012 12:11 PM
Points: 9, Visits: 52
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



Post #1046599
Posted Wednesday, January 12, 2011 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:35 PM
Points: 6, Visits: 106
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.
Post #1046685
Posted Tuesday, February 1, 2011 2:03 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 5:46 PM
Points: 25, Visits: 380
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
Post #1057060
Posted Wednesday, January 22, 2014 8:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:36 PM
Points: 80, Visits: 220
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
Post #1533689
Posted Wednesday, January 22, 2014 9:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 1,510, Visits: 8,470
The last script posted shows the Primary_Key_Ind = Yes for every column in my database.

That's definitely not correct!




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1533737
Posted Wednesday, January 22, 2014 10:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:36 PM
Points: 80, Visits: 220
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
Post #1533762
Posted Wednesday, January 22, 2014 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 1,510, Visits: 8,470
The above version seems to work.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1533781
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse