Documenting tables, columns and column attributes

This script reads the sysobject and syscolumns tables, createing a table with the tables' names, columns, type and sizes.

Before I write one line of code in a new SQL Server database, I run the following script. What this script does is read the sysobject and syscolumns tables, and creates a table with all of the tables' names, columns, type and sizes. This makes it so much easier when you're writing a bunch of stored procedures or you don't want to hunt down columns and tables from a schematic diagram.

Alerte Email

Inscrivez-vous pour recevoir régulièrement toute l’actualité IT.

Safe Harbor

DECLARE @TABLENAME VARCHAR(100)
DECLARE SOURCEMAP INSENSITIVE CURSOR FOR
    SELECT Name
    FROM sysobjects
    WHERE type = "U"
    ORDER BY name
    FOR READ ONLY

--Check to see if the table is present; if not, let's create it.

IF NOT EXISTS (SELECT * FROM sysobjects 
     WHERE id = object_id('dbo.TablesColumns'))

BEGIN
     CREATE TABLE TablesColumns(ColumnName VARCHAR(100),ColumnType 
         VARCHAR(30),TableName VARCHAR(100),ColumnSize INTEGER)
END

OPEN SOURCEMAP
  --Loop thru our cursor and insert our history and stage tale
  WHILE ( 1 = 1 )
    BEGIN    
      FETCH NEXT
       FROM SOURCEMAP
       INTO @TABLENAME

/*Bail out when all records have been inserted*/
IF (@@Fetch_Status <> 0) BREAK ELSE BEGIN /*Perform our select and insert*/ INSERT INTO TablesColumns (ColumnName, ColumnType,TableName,ColumnSize) SELECT SC.Name, CASE SC.XTYPE WHEN 189 THEN 'BINARY' WHEN 173 THEN 'BINARY' WHEN 52 THEN 'SMALLINT' WHEN 56 THEN 'INTEGER' WHEN 167 THEN 'VARCHAR' + '(' + CONVERT(VARCHAR,sc.Length) + ')' WHEN 175 THEN 'CHAR' + '(' + CONVERT(VARCHAR,sc.Length) + ')' WHEN 62 THEN 'FLOAT' WHEN 59 THEN 'REAL' WHEN 104 THEN 'BIT' WHEN 48 THEN 'TINYINT' WHEN 35 THEN 'TEXT' WHEN 99 THEN 'NTEXT' WHEN 61 THEN 'DATETIME' WHEN 60 THEN 'MONEY' WHEN 106 THEN 'DECIMAL' WHEN 108 THEN 'NUMERIC' WHEN 34 THEN 'IMAGE' WHEN 58 THEN 'SMALLDATETIME' WHEN 231 THEN 'NVARCHAR' + '(' + convert(varchar,sc.Length) + ')' END AS ColumnType, @TABLENAME AS [TableName], sc.Length AS [ColumnSize] FROM SYSCOLUMNS SC,SYSOBJECTS SO WHERE SO.ID = SC.ID AND SO.NAME =@TABLENAME /*If insert failed then raise error and rollback*/ END END Close SOURCEMAP DeAllocate SOURCEMAP

For More Information


This was first published in February 2005

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close