获取SQLServer的表信息

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