Darrell Norton's Blog [MVP]

Fill in description here...

Generate database schema information

Several times I’ve had to document an existing database structure and I didn’t have Visio or ERWin. So I created this stored proc to generate information on the tables in the stored procedure.

Just run the stored proc in Query Analyzer and it will return the table name, column name, data type, length (characters for char or varchar, precision and scale for decimals, and number of bytes for other data types), whether the column is part of the primary key, whether the column allows nulls, and what the default value is, if any.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDatabaseSchemaInformation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetDatabaseSchemaInformation]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE Procedure dbo.GetDatabaseSchemaInformation AS
select cols.TABLE_NAME As 'Table Name',
      cols.COLUMN_NAME as 'Column Name',
      cols.DATA_TYPE as 'Data Type',
                  case
                        when cols.DATA_TYPE = 'bigint' then '8 bytes'
                        when cols.DATA_TYPE = 'int' then '4 bytes'
                        when cols.DATA_TYPE = 'smallint' then '2 bytes'
                        when cols.DATA_TYPE = 'tinyint' then '1 byte'
                        when cols.DATA_TYPE = 'bit' then '1 byte'
                        when cols.DATA_TYPE = 'money' then '8 bytes'
                        when cols.DATA_TYPE = 'smallmoney' then '4 bytes'
                        when cols.DATA_TYPE = 'text' then '16 bytes'
                        when cols.DATA_TYPE = 'datetime' then '8 bytes'
                        when cols.DATA_TYPE = 'smalldatetime' then '4 bytes'
                        when cols.DATA_TYPE = 'uniqueidentifier' then '16 bytes'
                        when cols.DATA_TYPE = 'decimal' then convert (varchar,cols.NUMERIC_PRECISION) + ', ' + convert(varchar, cols.NUMERIC_SCALE)
                        else convert(varchar, cols.CHARACTER_MAXIMUM_LENGTH) + ' chars'
                  end as 'Length',
                  case
                        when kcu.CONSTRAINT_NAME is null then 'NO' else 'YES'
                  end as 'PK?',
                  upper(cols.IS_NULLABLE) as 'Allows Nulls',
                  isnull(cols.COLUMN_DEFAULT, '') as 'Default Value'
from INFORMATION_SCHEMA.COLUMNS cols
      join INFORMATION_SCHEMA.TABLES tabs on cols.TABLE_NAME = tabs.TABLE_NAME and
tabs.TABLE_TYPE = 'BASE TABLE'
      left join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on cols.TABLE_NAME = ctu.TABLE_NAME and ctu.CONSTRAINT_NAME like 'PK%'
      left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on ctu.CONSTRAINT_NAME =
kcu.CONSTRAINT_NAME and cols.COLUMN_NAME = kcu.COLUMN_NAME
where tabs.TABLE_NAME <> 'dtproperties'
order by cols.TABLE_NAME, cols.ORDINAL_POSITION

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Published Jun 24 2004, 07:26 AM by darrell

Comments

Garibaldi said:

# June 24, 2004 4:37 AM

Darrell said:

Garibaldi - thanks. Those typos must have been introduced when I pasted into the .TEXT new post form. Doh!

# June 24, 2004 4:56 AM

Garibaldi said:

Sure, no problem. Keep up the great blogging you've been doing. I visit often.

# June 24, 2004 5:23 AM

Darrell said:

Good to hear! Sometimes it feels like I'm writing to nobody. :)

# June 24, 2004 5:30 AM

Michael said:

You don't! :-)
Your blog have helped me alot! Thanks

# September 9, 2004 1:34 AM

Agi said:

I add the Column Description for the Last Column, and add the tablename as the parameter
so, the sp becomes ==>

create proc TableInfo (@tablename varchar(64)= null)
as
/*
Using Enterrise Manager Fill Column Description

*/
select cols.TABLE_NAME As 'Table Name',
cols.COLUMN_NAME as 'Column Name',
cols.DATA_TYPE as 'Data Type',
case
when cols.DATA_TYPE = 'bigint' then '8 bytes'
when cols.DATA_TYPE = 'int' then '4 bytes'
when cols.DATA_TYPE = 'smallint' then '2 bytes'
when cols.DATA_TYPE = 'tinyint' then '1 byte'
when cols.DATA_TYPE = 'bit' then '1 byte'
when cols.DATA_TYPE = 'money' then '8 bytes'
when cols.DATA_TYPE = 'smallmoney' then '4 bytes'
when cols.DATA_TYPE = 'text' then '16 bytes'
when cols.DATA_TYPE = 'datetime' then '8 bytes'
when cols.DATA_TYPE = 'smalldatetime' then '4 bytes'
when cols.DATA_TYPE = 'uniqueidentifier' then '16 bytes'
when cols.DATA_TYPE = 'decimal' then convert (varchar,cols.NUMERIC_PRECISION) + ', ' + convert(varchar, cols.NUMERIC_SCALE)
else convert(varchar, cols.CHARACTER_MAXIMUM_LENGTH) + ' chars'
end as 'Length',
case
when kcu.CONSTRAINT_NAME is null then 'NO' else 'YES'
end as 'PK?',
upper(cols.IS_NULLABLE) as 'Allows Nulls',
isnull(cols.COLUMN_DEFAULT, '') as 'Default Value'
, ( select value from sysproperties
where sysproperties.id = object_id(tabs.table_name)
and sysproperties.smallid = cols.ORDINAL_POSITION
and sysproperties.name = 'MS_Description' ) 'Column Description'
from INFORMATION_SCHEMA.COLUMNS cols
join INFORMATION_SCHEMA.TABLES tabs on cols.TABLE_NAME = tabs.TABLE_NAME and
tabs.TABLE_TYPE = 'BASE TABLE'
left join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on cols.TABLE_NAME = ctu.TABLE_NAME and ctu.CONSTRAINT_NAME like 'PK%'
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on ctu.CONSTRAINT_NAME =
kcu.CONSTRAINT_NAME and cols.COLUMN_NAME = kcu.COLUMN_NAME
where tabs.TABLE_NAME <> 'dtproperties'
and tabs.TABLE_NAME = isnull(@tablename, tabs.TABLE_NAME)
order by cols.TABLE_NAME, cols.ORDINAL_POSITION

# September 16, 2004 6:03 PM