This post originated from an RSS feed registered with .NET Buzz by Darrell Norton. Original Post: Generate database schema information Feed Title: Darrell Norton's Blog Feed URL: /error.htm?aspxerrorpath=/blogs/darrell.norton/Rss.aspx Feed Description: Agile Software Development: Scrum, XP, et al with .NET Latest .NET Buzz Posts Latest .NET Buzz Posts by Darrell Norton Latest Posts From Darrell Norton's Blog

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