Click here to Skip to main content
Click here to Skip to main content

Get SQL Server Table Information

By , 1 Jun 2012
Rate this:
Please Sign up or sign in to vote.

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.

License

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.

Comments and Discussions

 
GeneralThoughts PinmemberPIEBALDconsult1-Jun-12 5:08 
GeneralRe: Thoughts PinmemberDave 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 | Mobile
Web02 | 2.8.140421.2 | Last Updated 1 Jun 2012
Article Copyright 2012 by Dave Elliott
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid