• Posted by:
• Richard Lee
• 2 comments

Today I had a request to produce a report showing all the columns for the tables within a database. A interesting request and one that I was sure could be done, however it was the first time I had to do it. A bit of investigation led me to the nifty stored procedure.

    EXEC sp_help [TableName]

This produced a great result but I thought it would be great if I could do this automatically for all the tables in the database. This is what I came up with.

    DECLARE TablesCursor CURSOR
        FOR
            SELECT table_name AS Name
                FROM INFORMATION_SCHEMA.Tables;

    OPEN TablesCursor

    DECLARE @TableName sysname;
    FETCH NEXT FROM TablesCursor INTO @TableName;
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN;
        EXEC sp_help @TableName
        FETCH NEXT FROM TablesCursor INTO @TableName;
    END;

    CLOSE TablesCursor;
    DEALLOCATE TablesCursor;

While the output is good I needed a simple report that I could export to excel. So I decided to try another approach.

    SELECT
        table_name, 
        column_name,
        is_nullable,
        data_type,
        character_maximum_length,
        character_octet_length,
        numeric_precision,
        numeric_precision_radix,
        numeric_scale
    FROM INFORMATION_SCHEMA.COLUMNS
    ORDER BY table_name, ordinal_position

Very simple. Now rather than a fancy export I just copy and pasted!