Tip

Documenting tables, columns and column attributes

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'

    Requires Free Membership to View

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.

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.