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

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.

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.

SQL
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

SQL
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.

License

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_ -- http://www.sqlmag.com/article/tsql3/should-i-use-the-sp_-prefix-for-procedure-names-[^]

What you present is only useful with SQL Server -- a more database-agnostic (i.e. better) technique is to use the features in ADO.net that every provider is expected to implement, see:


http://msdn.microsoft.com/en-us/library/22936zd1[^]
and
http://msdn.microsoft.com/en-us/library/system.data.idatareader.getschematable[^]
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.