Looking for Schema/DDF Script
-
I have seen that the SSC (SQLServerCentral) site has many scripts dealing with schema of DB's and with generating a DDF (Data Dictionary File). I however can't seem to find any concensus on any one being the most robust or best to use. Instead of going thru everyone manually I was hoping one or more users here who have already done this or similiar and have a good idea of how each script compares with the others could point me in the direction of the best script to do the following:
Generate Meta data, a DDF (Data Dictionary File) like set of data stored within tables, that describes the schema including all columns in all tables. The schema should include the name & description of each table as well as the following about each column:
Name, Data Type & Size, Null option, Default Value, Identity Info, Formula (when applicable) & Description
I realize that no one script may provide all of the above about every column.
I also realize that there is no 'Descritpion' value for a column in the DB and that the 'Description' field shown in Ent Mgr for SQL Server is just meta data created/Used by ENt Mgr. What I'm looking for is something that clients who use the application developed by the company I work for, can use to easily get a DDF or blueprint of thier DB without having to manually build it. I know that SQL Server 2000 & Up has the ability to store Meta-Data and so I'm looking for a script that takes adavantage of that to store info within the DB for every column & table in a DB.
Any help is greatl;y appreciated.
Kindest Regards,
Just say No to Facebook! -
I haven't seen a good script to get this info, probably because it's a lot of work to store it in the first place. Everyone has to "build" their definitions first into the database somehow.
There are a few third party products you might check out as well. Apex SQL has one that might save time and I think it's reasonably priced.
-
I also realize that there is no 'Descritpion' value for a column in the DB and that the 'Description' field shown in Ent Mgr for SQL Server is just meta data created/Used by ENt Mgr. Not precisely correct...
The column Descriptions you are looking for are stored in the dbo.SysProperties table of each database. The ID in the table is the same ID (from SysObjects) as that of the table the property (description) belongs to and the SmallID column identifies the ColID (from SysColumns). If you filter the table by looking for 'MS_Description' in the Name column of SysProperties, you will get a list of all column descriptions available for all tables in the database. Join THAT up with SysObjects and SysColumns to complete the list.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Actually, I've resurrected some old code I was working on to make a WebTask that would make some pretty output... the output does not yet list the primary or other keys, but that's next... so is the documentation that I normally include in such a thing that is obviously missing from this... it's a start... perhaps combining this with sp_MSForEachTable will do the trick for you... (note that some of the variables are there for the conversion to a web task which I haven't done, yet)
DECLARE @TableName SYSNAME
SET @TableName = 'Orders'
DECLARE @NBSP NCHAR(6)
SET @NBSP = ' '
DECLARE @OutputFile NVARCHAR(255)
SET @OutputFile = 'C:\WEB\TableSchema'+@TableName+'.htm'
DECLARE @WebPageTitle SYSNAME
SET @WebPageTitle = 'Table Schema - '+@TableName
DECLARE @ResultsTitle SYSNAME
SET @ResultsTitle = 'DB\Table Schema - '+DB_NAME()+'\'+@TableName
DECLARE @Query VARCHAR(8000)
DECLARE @SortKey VARCHAR(10)
SET @SortKey = 'Name'
SELECT Name = obj.Name,
Owner = USER_NAME(UID),
Type = UPPER(SUBSTRING(sptv.Name,5,31)),
Created = obj.CRDate
FROM dbo.SysObjects obj,
MASTER.dbo.spt_Values sptv
WHERE obj.Name = @TableName
AND sptv.Type = 'O9T'
AND obj.XType = SUBSTRING(sptv.Name,1,2) COLLATE Database_Default
SELECT ColID = STR(col.ColID,5),
ColName = col.Name,
DataType = UPPER(typ.Name)
+ CASE
WHEN dtyp.Create_Params IS NULL
THEN ''
WHEN dtyp.Create_Params = 'Precision,Scale'
THEN '('+LTRIM(STR(col.XPrec))+','+LTRIM(STR(col.XScale))+')'
WHEN dtyp.Create_Params IN ('Max Length','Length')
THEN '('+LTRIM(STR(col.Length))+')'
ELSE '(Unknown)'
END,
AutoPopulate = CASE
WHEN com.ID IS NOT NULL
THEN 'DEFAULT ' + com.Text
WHEN col.ColStat & 1 = 1
THEN 'IDENTITY('+LTRIM(STR(IDENT_SEED(obj.Name)))+','+LTRIM(STR(IDENT_INCR(obj.Name)))+')'
WHEN col.IsComputed = 1
THEN 'COMPUTED'
ELSE ''
END,
Nulls = CASE
WHEN col.IsNullable = 0
THEN 'NOT NULL'
ELSE ''
END,
Description = ISNULL(prop.Value,'')
FROM dbo.SysObjects obj,
dbo.SysTypes typ,
MASTER.dbo.spt_DataType_Info dtyp,
dbo.SysColumns col
LEFT OUTER JOIN
dbo.SysComments com
ON col.cDefault = com.ID
AND com.ColID = 1
LEFT OUTER JOIN
dbo.SYSPROPERTIES prop
ON prop.ID = col.ID
AND prop.SmallID = col.ColID
AND prop.Name = 'MS_Description'
WHERE col.ID = obj.ID
AND col.XUserType = typ.XUserType
AND typ.XType = dtyp.ss_DType
AND ( dtyp.ODBCVer IS NULL
OR dtyp.ODBCVer = 2)
AND ( dtyp.Auto_Increment IS NULL
OR dtyp.Auto_Increment = 0)
AND obj.Name = @TableName
ORDER BY col.Name
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's my version:
CREATE TABLE [dbo].TableSchema(
[table_name] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_order] [smallint] NULL,
[column_name] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_datatype] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_length] [smallint] NULL,
[column_precision] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_scale] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_allownull] [varchar](254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_default] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column_description] [sql_variant] NULL
) ON [PRIMARY]
----------------------------------------------------
DECLARE @table_name nvarchar(128)
Set Nocount ON
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
INSERT into TableSchema
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))) as 'column_allownull',
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 collate SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN syscomments com ON col.cdefault = com.id
WHERE obj.name = @table_name AND typ.[name] <> 'sysname'
ORDER BY col.colorder
FETCH NEXT FROM tablenames_cursor INTO @table_name
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
---------------
select * from TableSchema order by table_name
-
Homebrew01,
Kindest Regards,
Just say No to Facebook!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply