SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL SERVER 2008 Data Dictionary


SQL SERVER 2008 Data Dictionary

Author
Message
Abdullah.Khan
Abdullah.Khan
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 73
Comments posted to this topic are about the item SQL SERVER 2008 Data Dictionary
hjm
hjm
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.



ACinAZ
ACinAZ
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 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'

hjm
hjm
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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



ELLISW
ELLISW
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 107
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.
ayesamson
ayesamson
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 521
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
oradbguru
oradbguru
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 316
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
Alvin Ramard
Alvin Ramard
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15886 Visits: 11725
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.

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
oradbguru
oradbguru
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 316
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
Alvin Ramard
Alvin Ramard
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15886 Visits: 11725
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.

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search