Click here to Skip to main content
15,883,558 members
Articles / Database Development / SQL Server

Get SQL Server Table Information

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
1 Jun 2012CPOL1 min read 15.3K   3   2
Global Stored Procedure to retrieve tabular information or a CSV list of columns for a database table.


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


USE master

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

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

     @tableName      VARCHAR(255)
    ,@display        TINYINT  = 0
    ,@orderByName    BIT      = 0

    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, AS column_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 = @tableName

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

EXEC sys.sp_MS_marksystemobject sp_GTC

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior) Webbert Solutions
United States United States
Dave is an independent consultant working in a variety of industries utilizing Microsoft .NET technologies.

Comments and Discussions

GeneralThoughts Pin
PIEBALDconsult1-Jun-12 5:08
mvePIEBALDconsult1-Jun-12 5:08 
Don't prefix your stored procedure names with sp_ --[^]

What you present is only useful with SQL Server -- a more database-agnostic (i.e. better) technique is to use the features in that every provider is expected to implement, see:[^]
GeneralRe: Thoughts Pin
Dave Elliott1-Jun-12 6:41
Dave Elliott1-Jun-12 6:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.