SQL Serverのテーブル情報を取得する用スクリプト

2014年6月24日

1.処理コード：

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: starnews24.com
— Create date: 23/06/2014
— 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

2.利用方法：

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