- 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
- --该片段来自于http://www.codesnippet.cn/detail/210320132504.html
来源: http://www.codesnippet.cn/detail/210320132504.html