01.03.08
Useful and Important Query For DataDictionary
The following script will help you sometime to create Data-Dictionary of a Table or stored Procedure. Hope it will helpful for all.
Script For Table:
/*Purpose : This Script will help you to create DataDictionary of a Database. By using this Script you will get all the Column Name,Datatype,DataTypeLength and Nullability of Column
How To Use: Before Execution of this change the Database Name and it will only work in SQL Server 2005 Copy it in Excel WorkSheet then Convert it from text to column by Specifying Delimeter as Tab and Comma(,) then you can identfy more easily.
*/
USE [AdventureWorks] SET NOCOUNT ON
CREATE TABLE #T ( Id INT IDENTITY(1,1), ObjId INT NOT NULL)
INSERT INTO #T
SELECT OBJECT_ID FROM SYS.TABLES
ORDER BY OBJECT_NAME(OBJECT_ID) ASC DECLARE @tot INT, @i INTSET @i = 1 SELECT @tot =MAX(id)
FROM #t WHILE (@i<=@tot AND @i>0)
BEGIN DECLARE @objid INT SELECT @objid = Objid FROM #t
WHERE id=@i DECLARE @objname VARCHAR(255) SELECT @objname=OBJECT_NAME(OBJECT_ID) FROM SYS.Tables
WHERE OBJECT_ID = @objid PRINT @objname PRINT ” DECLARE @totparam INT, @minparam INT SELECT @totparam = MAX(column_id), @minparam = MIN(Column_id)
FROM SYS.COLUMNS WHERE OBJECT_ID = @objid PRINT ‘Column Name’+‘ , ‘+‘Data Type’+‘ , ‘+‘Size’+‘ , ‘+‘Nullability’+‘ , ‘+‘Remarks’ PRINT ” WHILE (@minparam<=@totparam) BEGIN DECLARE @Colname CHAR(45), @ColDtype CHAR(16), @length INT, @Nullability
VARCHAR(30) SELECT @Colname = sc.name, @ColDtype = UPPER(sd.name), @length = sc.max_length, @Nullability
= ( CASE sc.is_nullable WHEN 0 THEN ‘No’
ELSE ‘Yes’ END ) FROM SYS.COLUMNS SC JOIN SYS.TYPES sd
ON sc.system_type_id = sd.system_type_id WHERE sc.OBJECT_ID = @objid AND sc.column_id = @minparam ORDER BY sc.column_id ASC
PRINT @Colname+‘ , ‘ +@ColDtype+‘ , ‘ +CONVERT(VARCHAR(10),@length)+‘ , ‘ +@Nullability SET @minparam = @minparam + 1 END SET @i=@i+1PRINT ”
END GO
Script For Stored Procedure:
/*Purpose : This Script will help you to create DataDictionary of a Database. By using this Script you
will get all the Parameter Name,Datatype,DataTypeLength and Outputability of Column
How To Use: Before Execution of this change the Database Name and it will only work in SQL Server 2005
Copy it in Excel WorkSheet then Convert it from text to column by Specifying Delimeter as
Tab and Comma(,) then you can identfy more easily.
IMP : Please Before ReRun Check that temp table #t and drop it first
*/
USE [AdventureWorks] SET NOCOUNT ON
DROP TABLE #T CREATE TABLE #T ( Id
INT IDENTITY(1,1), Objid INT NOT NULL) INSERT
INTO #T SELECT OBJECT_ID
FROM SYS.PROCEDURES ORDER BY OBJECT_NAME(OBJECT_ID) ASC DECLARE @tot INT, @i INTSET @i= 1
SELECT @tot=MAX(id) FROM #t
WHILE (@i<=@tot AND @i > 0) BEGIN DECLARE @objid INT SELECT @objid= Objid
FROM #t WHERE id=@i
DECLARE @objname VARCHAR(255) SELECT @objname=OBJECT_NAME(OBJECT_ID)
FROM SYS.PROCEDURES WHERE OBJECT_ID = @objid PRINT @objname DECLARE @totparam INT, @minparam INT
SELECT @totparam = MAX(parameter_id) FROM SYS.ALL_PARAMETERS
WHERE OBJECT_ID = @objid SELECT @minparam = MIN(parameter_id)
FROM SYS.ALL_PARAMETERS WHERE OBJECT_ID = @objid
PRINT ” PRINT ‘Parameter Name’+‘ , ‘+‘Data Type’+‘ , ‘+‘Size’+‘ , ‘+‘OutPut’+‘ , ‘+‘Remarks’
WHILE (@minparam<=@totparam) BEGIN DECLARE @paramname CHAR(45), @paramDtype CHAR(16), @paramlength INT, @paramoutput VARCHAR(30)
SELECT @paramname = sa.Name, @paramDtype = UPPER(st.name), @paramlength = st.max_length, @paramoutput = (
CASE sa.is_output WHEN 0 THEN ‘No’ ELSE ‘True’END )
FROM SYS.ALL_PARAMETERS sa JOIN SYS.TYPES st
ON sa.System_type_id = st.user_type_id WHERE sa.object_id =@objid AND sa.parameter_id = @minparam
PRINT @paramname+‘ , ‘+@paramDtype+‘ , ‘+CONVERT(VARCHAR(10),@paramlength)+‘ , ‘+@paramoutput SET @minparam=@minparam+ 1 END
SET @i=@i+1 PRINT ”
ENDGO
