Login
Important alert: (current site time 7/15/2013 7:19:41 PM EDT)
 

VB icon

Hot Data Dictionary

 Email
Submitted on: 1/14/2008 8:37:51 AM
By: Raghavendra Narayana Upadhya 
Level: Advanced
User Rating: By 1 Users
Compatibility: SQL Server 2000
Views: 7039
author picture
(About the author)
 
     Simple query to get the Data Dictionary within seconds for data modeling study purpose. Compatible with SQL Server 2005 and 2000.

 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description. 
				
--**************************************
-- Name: Hot Data Dictionary
-- Description:Simple query to get the Data Dictionary within seconds for data modeling study purpose. Compatible with SQL Server 2005 and 2000.
-- By: Raghavendra Narayana Upadhya
--
-- Inputs:No Inputs required. If you want to look for specific tables, columns use the where conditions on the columns included in SELECT part.
--
-- Returns:Returns recordset about Tables and Columns like similar to general Data Dictionary.
--
-- Assumes:Nothing.
--
-- Side Effects:Nothing. System tables are used for select purpose. Do not mess up with System tables. Just run the select query.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1204&lngWId=5--for details.--**************************************

--HOT DATA DICTIONARY
---SQL QUERY
select
o.[name] as 'Table_Name',
c.[name] as 'Column_Name',
t.[name] as 'Column_Datatype',
case when (c.ISNULLABLE = 0) then 'NO' else 'YES' END AS 'Is_Nullable',
case when (t.[name] = 'numeric') then '' else c.[length] end as 'Column_Length',
case when (t.[name] != 'numeric') then '' else c.prec end as 'Column_Precision',
case when (t.[name] != 'numeric') then '' else c.scale end as 'Column_Scale',
'' as 'Column_Description',
case when (pks.pkscolid is not null) then 'YES' else '' end as 'Is_PK',
case when (vfk.ref_table_name is not null) then 'YES' else '' end as 'Is_FK',
ISNULL(vfk.REF_TABLE_NAME, '') as 'Referenced_Table',
ISNULL(vfk.REF_COLUMN_NAME, '') as 'Referenced_Column',
case when (Select count(1) from sysindexkeys WITH (NOLOCK) where id = c.id and colid = c.colid)>0 then 'YES' else '' END AS 'Column_Has_Any_Index'
from sysobjects o WITH (NOLOCK) 
inner join syscolumns C WITH (NOLOCK) on o.id = c.id 
inner join systypes t WITH (NOLOCK) on c.xtype = t.xtype
left outer join (
SELECT 
--Name of the FOREIGN KEY constraint 
SO3.NAME FK_NAME, 
--Unique ID of FOREIGN KEY constraint 
SO3.ID FK_ID, 
-- Owner of the table with the FOREIGN KEY constraint 
SU.NAME TABLE_OWNER, 
--Name of the table with the FOREIGN KEY constraint 
SO.NAME TABLE_NAME, 
--Object identification number. 
SO.ID TABLE_ID, 
--Name of the Column with the FOREIGN KEY constraint 
SC.NAME COLUMN_NAME, 
--Position of the column in the FOREIGN KEY constraint 
SC.COLID COLID, 
--Owner of the table referenced in the FOREIGN KEY constraint 
SU2.NAME REF_TABLE_OWNER, 
--Name of the table referenced in the FOREIGN KEY constraint 
SO2.NAME REF_TABLE_NAME, 
--Object identification number of the table referenced in the FOREIGN KEY constraint. 
SO2.ID REF_TABLE_ID, 
--Name of the column referenced in the FOREIGN KEY constraint 
SC2.NAME REF_COLUMN_NAME, 
--Position of the column in the reference column list 
SC2.COLID REF_TABLE_COLID 
 
FROM SYSFOREIGNKEYS SYSFK WITH (NOLOCK)
--Foreign Key Constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE XTYPE = 'U') SO 
ON SYSFK.FKEYID = SO.ID 
--Referenced in the FOREIGN KEY constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE XTYPE = 'U') SO2 
ON SYSFK.RKEYID = SO2.ID 
--Foreign Key Constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS WITH (NOLOCK)) SC ON SYSFK.FKEYID = 
SC.ID AND SYSFK.FKEY = SC.COLID 
--Referenced in the FOREIGN KEY constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS WITH (NOLOCK)) SC2 ON SYSFK.RKEYID 
= SC2.ID AND SYSFK.RKEY = SC2.COLID 
--Name and ID of the FOREIGN KEY constraint 
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS WITH (NOLOCK)) SO3 ON SYSFK.CONSTID = 
SO3.ID 
--Foreign Key Constraint - Owner info 
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID 
--Referenced in the FOREIGN KEY constraint - Owner info 
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID 
) vfk
on c.id = vfk.table_id and c.colid = vfk.colid
left outer join
(select o.id pksid, c.colid pkscolid
from sysindexes i WITH (NOLOCK)
join sysobjects o WITH (NOLOCK) ON i.id = o.id
join sysobjects pk WITH (NOLOCK) ON i.name = pk.name
AND pk.parent_obj = i.id
AND pk.xtype = 'PK'
join sysindexkeys ik WITH (NOLOCK) on i.id = ik.id
and i.indid = ik.indid
join syscolumns c WITH (NOLOCK) ON ik.id = c.id
AND ik.colid = c.colid) pks
on c.id = pks.pksid AND c.colid = pks.pkscolid
where o.type = 'u'
and o.name not like 'sysdiagrams'
order by o.[name]
----***********------SQL QUERY ENDS HERE.
-------------------------------------------------------------------------------------
--********CREATE A VIEW AND USE THE VIEW INSTEAD OF THE ABOVE BIG QUERY IF REQUIRED.*******
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vdd] as
select
o.[name] as 'Table_Name',
c.[name] as 'Column_Name',
t.[name] as 'Column_Datatype',
case when (c.ISNULLABLE = 0) then 'NO' else 'YES' END AS 'Is_Nullable',
case when (t.[name] = 'numeric') then '' else c.[length] end as 'Column_Length',
case when (t.[name] != 'numeric') then '' else c.prec end as 'Column_Precision',
case when (t.[name] != 'numeric') then '' else c.scale end as 'Column_Scale',
'' as 'Column_Description',
case when (pks.pkscolid is not null) then 'YES' else '' end as 'Is_PK',
case when (vfk.ref_table_name is not null) then 'YES' else '' end as 'Is_FK',
ISNULL(vfk.REF_TABLE_NAME, '') as 'Referenced_Table',
ISNULL(vfk.REF_COLUMN_NAME, '') as 'Referenced_Column',
case when (Select count(1) from sysindexkeys WITH (NOLOCK) where id = c.id and colid = c.colid)>0 then 'YES' else '' END AS 'Column_Has_Any_Index'
from sysobjects o WITH (NOLOCK) 
inner join syscolumns C WITH (NOLOCK) on o.id = c.id 
inner join systypes t WITH (NOLOCK) on c.xtype = t.xtype
left outer join (
SELECT 
--Name of the FOREIGN KEY constraint 
SO3.NAME FK_NAME, 
--Unique ID of FOREIGN KEY constraint 
SO3.ID FK_ID, 
-- Owner of the table with the FOREIGN KEY constraint 
SU.NAME TABLE_OWNER, 
--Name of the table with the FOREIGN KEY constraint 
SO.NAME TABLE_NAME, 
--Object identification number. 
SO.ID TABLE_ID, 
--Name of the Column with the FOREIGN KEY constraint 
SC.NAME COLUMN_NAME, 
--Position of the column in the FOREIGN KEY constraint 
SC.COLID COLID, 
--Owner of the table referenced in the FOREIGN KEY constraint 
SU2.NAME REF_TABLE_OWNER, 
--Name of the table referenced in the FOREIGN KEY constraint 
SO2.NAME REF_TABLE_NAME, 
--Object identification number of the table referenced in the FOREIGN KEY constraint. 
SO2.ID REF_TABLE_ID, 
--Name of the column referenced in the FOREIGN KEY constraint 
SC2.NAME REF_COLUMN_NAME, 
--Position of the column in the reference column list 
SC2.COLID REF_TABLE_COLID 
 
FROM SYSFOREIGNKEYS SYSFK WITH (NOLOCK)
--Foreign Key Constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE XTYPE = 'U') SO 
ON SYSFK.FKEYID = SO.ID 
--Referenced in the FOREIGN KEY constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE XTYPE = 'U') SO2 
ON SYSFK.RKEYID = SO2.ID 
--Foreign Key Constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS WITH (NOLOCK)) SC ON SYSFK.FKEYID = 
SC.ID AND SYSFK.FKEY = SC.COLID 
--Referenced in the FOREIGN KEY constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS WITH (NOLOCK)) SC2 ON SYSFK.RKEYID 
= SC2.ID AND SYSFK.RKEY = SC2.COLID 
--Name and ID of the FOREIGN KEY constraint 
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS WITH (NOLOCK)) SO3 ON SYSFK.CONSTID = 
SO3.ID 
--Foreign Key Constraint - Owner info 
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID 
--Referenced in the FOREIGN KEY constraint - Owner info 
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID 
) vfk
on c.id = vfk.table_id and c.colid = vfk.colid
left outer join
(select o.id pksid, c.colid pkscolid
from sysindexes i WITH (NOLOCK)
join sysobjects o WITH (NOLOCK) ON i.id = o.id
join sysobjects pk WITH (NOLOCK) ON i.name = pk.name
AND pk.parent_obj = i.id
AND pk.xtype = 'PK'
join sysindexkeys ik WITH (NOLOCK) on i.id = ik.id
and i.indid = ik.indid
join syscolumns c WITH (NOLOCK) ON ik.id = c.id
AND ik.colid = c.colid) pks
on c.id = pks.pksid AND c.colid = pks.pkscolid
where o.type = 'u'
and o.name not like 'sysdiagrams'
--order by o.[name]

Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:
Your Vote

What do you think of this code (in the Advanced category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 
Other User Comments
1/7/2010 7:00:34 AM: jim@hsd.com.au

Use this join amendment

was

INNER JOIN systypes t WITH (NOLOCK) on c.xtype = t.xtype

now

INNER JOIN systypes t WITH (NOLOCK) on c.xtype = t.xusertype

otherwise you are not allowing for user types.



(If this comment was disrespectful, please report it.)
 
Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.

Search:

Newest postings for SQL.

Add this ticker to my site

Daily code email

Click here to join the 'Code of the Day' mailing list!

This site on CD

Over 7,000 submissions on a super fast CD!

Code/Articles

Top Code

Community

Jobs

Other

Goto…

Copyright© 1997-2013 by Exhedra Solutions, Inc. All Rights Reserved.
By using this site you agree to its Terms and Conditions. Planet Source Code™ and the phrase "Dream It. Code It"™ are trademarks of Exhedra Solutions, Inc.