Schema/Data Dictionary/Table Definitions Query
-
Does anyone have a sample SQL program that outputs table and field definitions in a given database, into CSV format?
-
I copied this script from this forum awhile back. It may be what you're looking for.
Run the first part in Query Analyzer to create a procedure. Then execute the procedure (last 3 lines below)
Posted with thanks to Ramesh Kondaparthy and Steven Steinberg .
/*
--By: Steven Steinberg
--Based on Ramesh Kondaparthy's script (Script to Generate DataDictionary for Database, posted 8/5/2005)
--I made a few format changes and included a few more columns.
--I modified Ramesh's script so that I could copy the result and paste it into Word as the beginning of
--a Data Dictionary document.
--(Hint: Once you've pasted the result into Word, do a Select All then an Insert Table.)
--USAGE
-- EXEC Generate_getDataDictionary
*/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Generate_getDataDictionary
AS
DECLARE @table_name nvarchar(128)
Set Nocount ON
CREATE table #tblDataDictionary
(table_name [sql_variant],
column_order [sql_variant],
column_name [sql_variant],
column_datatype [sql_variant],
column_length [sql_variant],
column_precision [sql_variant],
column_scale [sql_variant],
column_allownull [sql_variant],
column_default [sql_variant],
column_description [sql_variant])
DECLARE tablenames_cursor CURSOR FOR
SELECT name FROM sysobjects where type = 'U' and status > 1 order by name
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
--CODE FOR THE COLUMNS
INSERT INTO #tblDataDictionary
SELECT
obj.[name] AS 'table_name',
col.colorder AS 'column_order',
col.[name] AS 'column_name',
typ.[name] AS 'column_datatype',
col.[length] AS 'column_length',
CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[prec] AS nvarchar(255)) ELSE '' END AS 'column_precision',
CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[scale] AS nvarchar(255)) ELSE '' END AS 'column_scale',
convert(varchar(254), rtrim(substring(' YES',(ColumnProperty (col.id, col.name,'AllowsNull')*3)+1,3))),
ISNULL(com.text,'') AS 'column_default',
ISNULL(ext.value,'') AS 'column_description'
FROM sysobjects obj
INNER join syscolumns col on obj.id = col.id
INNER JOIN systypes typ
ON col.xtype = typ.xtype
LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @table_name, N'column', null) ext on col.name = ext.objname
LEFT OUTER JOIN syscomments com ON col.cdefault = com.id
WHERE obj.name = @table_name
AND typ.[name] <> 'sysname'
ORDER BY col.colorder
--CODE ENDS HERE
FETCH NEXT FROM tablenames_cursor INTO @table_name
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
SELECT * FROM #tblDataDictionary ORDER BY table_name,Column_Order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Within Query Analyzer, click Query on the menu bar, select Results To Text
--Then run this line
exec Generate_getDataDictionary
-
Great! I will try this out.
Thanks for your help.
-
how do i get the column description filled in when i create the tables?
-
"how do i get the column description filled in when i create the tables? "
The simplest way is to open the table for Design in Enterprise Manager and just type it in for each column.
For other options, check out the BOL subject "Using Extended Properties on Database Objects" When you open BOL, paste that title in the Search box. When the results are returned, click on the title column to sort the results and find the subject.
Hope this helps.
-
Excellent! Thanks! now i can set up my DDL to document whats in the database automagically!
-
I wrote a similar utility, and I use it to compare databases, for example to check for differences between TEST & PROD. Just modify the above to output to tables, then compare your output tables for differences.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply