Click here to Skip to main content
11,630,144 members (79,335 online)
Click here to Skip to main content

Get SQL Server Table Information

, 1 Jun 2012 CPOL 7.5K 3
Rate this:
Please Sign up or sign in to vote.
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)


About the Author

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

You may also be interested in...

Comments and Discussions

GeneralThoughts Pin
PIEBALDconsult1-Jun-12 5:08
memberPIEBALDconsult1-Jun-12 5:08 
GeneralRe: Thoughts Pin
Dave Elliott1-Jun-12 6:41
memberDave Elliott1-Jun-12 6:41 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150723.1 | Last Updated 1 Jun 2012
Article Copyright 2012 by Dave Elliott
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid