获取 SQL Server 表信息的存储过程

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

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