Get SQL Server Table Information

Introduction

I consider myself a lazy programmer in the sense that I don't like doing things repetitively without having a shortcut or template in place to take out the monotonous nature of the task.

I’ve had a Stored Procedure to gather database table information but it was lacking in the sense that I had to always add it to the database that I was interested in gathering information about and I didn’t have it create a CSV of the column names.

So I found a way to address both of these issues. The first, creating a CSV list was easy enough. The second issue of add it once and use everywhere was a little more challenging. After some Googling and some trial and error I came up with the script below.

Using the code

Within SQL Server Management Studio, open a new query window and set the dropdown to the database you want to use or you can do "USE <database>" before executing the stored procedure.

To run the stored procedure, enter one following formats with your table name.

exec sp_gtc 'Employee'         Tabular/Unsorted
exec sp_gtc 'Employee' 0, 1    Tabular/Sorted
exec sp_gtc 'Employee' 1, 0    CSV/Unsorted
exec sp_gtc 'Employee' 1, 1    CSV/Sorted

Script

USE master
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GTC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GTC]
GO

-- =============================================
-- Author:      David Elliott
-- Create date: 05/01/2012
-- Description: Return table information
--
-- INPUT
--    @tableName      Name of the table to get information about
--    @display        0 = Tabular, 1 = CSV
--    @orderByName    0 = No,      1 = Yes
-- =============================================

CREATE PROCEDURE sp_GTC 
     @tableName      VARCHAR(255)
    ,@display        TINYINT  = 0
    ,@orderByName    BIT      = 0
AS
    SET NOCOUNT ON

    DECLARE @tableColumns TABLE
    (
         column_id      INT
        ,column_name    VARCHAR(200)
        ,dataType       VARCHAR(200)
        ,max_length     INT
        ,precision      TINYINT
        ,scale          INT
        ,is_nullable    BIT
        ,is_identity    BIT
    )

    INSERT INTO @tableColumns (c.column_id, column_name, dataType, max_length, precision, scale, is_nullable, is_identity)
    SELECT c.column_id, c.name AS column_name, ct.name as dataType, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity
    FROM sys.columns c
        INNER JOIN sys.tables t ON c.object_id = t.object_id
        INNER JOIN sys.types ct ON c.system_type_id = ct.system_type_id
    WHERE t.name = @tableName

    IF (@display = 0)
    BEGIN
        SELECT * 
        FROM @tableColumns 
        ORDER BY CASE WHEN @orderByName = 0 
            THEN REPLACE(STR(column_id, 4), SPACE(1), '0') 
            ELSE column_name 
            END
    END
    ELSE IF (@display = 1)
    BEGIN
        SELECT SUBSTRING(
        (
            SELECT ', ' + column_name
            FROM @tableColumns
            ORDER BY CASE WHEN @orderByName = 0 
                THEN REPLACE(STR(column_id, 4), SPACE(1), '0') 
                ELSE column_name 
                END
            FOR XML PATH('')
        ), 2, 200000) AS CSV
    END
GO

EXEC sys.sp_MS_marksystemobject sp_GTC
GO

Points of Interest

If you are looking to create your own global stored procedure, I found that the name must begin with “sp_”.  You will also need to call sys.sp_MS_marksystemobject with your own stored procedure name as in the script above.