擷取資料庫表的一些資訊：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDatabaseSchemaInformation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetDatabaseSchemaInformation]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE Procedure dbo.GetDatabaseSchemaInformation AS
select cols.TABLE_NAME As 'Table Name',
cols.COLUMN_NAME as 'Column Name',
cols.DATA_TYPE as 'Data Type',
case
when cols.DATA_TYPE = 'bigint' then '8 bytes'
when cols.DATA_TYPE = 'int' then '4 bytes'
when cols.DATA_TYPE = 'smallint' then '2 bytes'
when cols.DATA_TYPE = 'tinyint' then '1 byte'
when cols.DATA_TYPE = 'bit' then '1 byte'
when cols.DATA_TYPE = 'money' then '8 bytes'
when cols.DATA_TYPE = 'smallmoney' then '4 bytes'
when cols.DATA_TYPE = 'text' then '16 bytes'
when cols.DATA_TYPE = 'datetime' then '8 bytes'
when cols.DATA_TYPE = 'smalldatetime' then '4 bytes'
when cols.DATA_TYPE = 'uniqueidentifier' then '16 bytes'
when cols.DATA_TYPE = 'decimal' then convert (varchar,cols.NUMERIC_PRECISION) + ', ' + convert(varchar, cols.NUMERIC_SCALE)
else convert(varchar, cols.CHARACTER_MAXIMUM_LENGTH) + ' chars'
end as 'Length',
case
when kcu.CONSTRAINT_NAME is null then 'NO' else 'YES'
end as 'PK?',
upper(cols.IS_NULLABLE) as 'Allows Nulls',
isnull(cols.COLUMN_DEFAULT, '') as 'Default Value'
from INFORMATION_SCHEMA.COLUMNS cols
join INFORMATION_SCHEMA.TABLES tabs on cols.TABLE_NAME = tabs.TABLE_NAME and
tabs.TABLE_TYPE = 'BASE TABLE'
left join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on cols.TABLE_NAME = ctu.TABLE_NAME and ctu.CONSTRAINT_NAME like 'PK%'
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on ctu.CONSTRAINT_NAME =
kcu.CONSTRAINT_NAME and cols.COLUMN_NAME = kcu.COLUMN_NAME
where tabs.TABLE_NAME <> 'dtproperties'
order by cols.TABLE_NAME, cols.ORDINAL_POSITION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
