1、获取指定表、视图的所有字段属性
只要输入不同的表/视图名，就可以获取该表的所有字段名字、字段长度、字段类型、字段说明、字段是否允许为空、是否主键、是否自增长字段等。
USE [数据库名称]GO/****** Object: StoredProcedure [dbo].[A_P_GetColumnStructureInfo] Script Date: 11/19/2015 16:11:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[A_P_GetColumnStructureInfo] ( @tableName NVARCHAR(500) -- 表名 )AS DECLARE @sqlTemp NVARCHAR(MAX); --查询sql SET @sqlTemp = 'SELECT syscolumns.name AS Code , syscolumns.name AS Name , IsPrimaryKey = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects INNER JOIN sysindexes ON sysindexes.name = sysobjects.name INNER JOIN sysindexkeys ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid WHERE xtype = ''PK'' AND parent_obj = syscolumns.id AND sysindexkeys.colid = syscolumns.colid ) THEN 1 ELSE 0 END , systypes.name AS DataType , syscolumns.length AS N''DataLength'', --sys.extended_properties.value AS Mark , ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, ''Scale''), 0) AS N''Pricision'' , ISNULL(syscomments.text, '''') N''DefaultValue'' , syscolumns.isnullable AS IsNotNull , ''0'' AS N''IsUnique'' , NEWID() AS N''id'' , ''0'' AS N''IsSystem'' , IsIncrementColumn = CASE syscolumns.status WHEN 128 THEN 1 ELSE 0 END, ISNULL(extended_properties.[value], '''') AS N''Remark'' , ''0'' AS N''DataObjectId'', 0 AS IdentityIncrement, 0 AS IDENT_SEED FROM syscolumns INNER JOIN systypes ON ( syscolumns.xtype = systypes.xtype AND systypes.name <> ''_default_'' AND systypes.name <> ''sysname'' ) left join syscomments on syscolumns.cdefault = syscomments.id LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.major_id = syscolumns.id AND minor_id = syscolumns.colid ) WHERE syscolumns.id = ( SELECT id FROM sysobjects WHERE name = [email protected]+''' )ORDER BY syscolumns.colid; 'PRINT @sqlTemp;------返回查询结果-----EXEC sp_executesql @sqlTemp;GO
小注：
0 AS IdentityIncrement,0 AS IDENT_SEED
这两个是充数，想获取真实值，请用2、3的函数
升级版（修改IsIncrementColumn列为bit类型）：
USE [AMACDBtest]GO/****** Object: StoredProcedure [dbo].[A_P_GetColumnStructureInfo] Script Date: 11/20/2015 09:58:03 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter PROCEDURE [dbo].[A_P_GetColumnStructureInfo] ( @tableName NVARCHAR(MAX) -- 表名 )AS DECLARE @sqlTemp NVARCHAR(MAX); --查询sql SET @sqlTemp = 'SELECT syscolumns.name AS Code , syscolumns.name AS Name , IsPrimaryKey = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects INNER JOIN sysindexes ON sysindexes.name = sysobjects.name INNER JOIN sysindexkeys ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid WHERE xtype = ''PK'' AND parent_obj = syscolumns.id AND sysindexkeys.colid = syscolumns.colid ) THEN 1 ELSE 0 END , systypes.name AS DataType , syscolumns.length AS N''DataLength'', --sys.extended_properties.value AS Mark , ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, ''Scale''), 0) AS N''Pricision'' , ISNULL(syscomments.text, '''') N''DefaultValue'' , syscolumns.isnullable AS IsNotNull , ''0'' AS N''IsUnique'' , NEWID() AS N''id'' , ''0'' AS N''IsSystem'' , --IsIncrementColumn = CASE syscolumns.status -- WHEN 128 THEN 1 -- ELSE 0 -- END, IsIncrementColumn = cast(CASE syscolumns.status WHEN 128 THEN 1 ELSE 0 END AS bit), ISNULL(extended_properties.[value], '''') AS N''Remark'' , ''0'' AS N''DataObjectId'', 0 AS IdentityIncrement, 0 AS IDENT_SEED FROM syscolumns INNER JOIN systypes ON ( syscolumns.xtype = systypes.xtype AND systypes.name <> ''_default_'' AND systypes.name <> ''sysname'' ) left join syscomments on syscolumns.cdefault = syscomments.id LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.major_id = syscolumns.id AND minor_id = syscolumns.colid ) WHERE syscolumns.id = ( SELECT id FROM sysobjects WHERE name = [email protected]+''' )ORDER BY syscolumns.colid; 'PRINT @sqlTemp;------返回查询结果-----EXEC sp_executesql @sqlTemp;GO
2、获取标识列的种子值（标识种子：指示标识列的初始行值。标识种子必须是 整数，位数等于或小于 10。）
可使用函数IDENT_SEED,用法：
SELECT IDENT_SEED ('表名')3、获取标识列的递增量（标识增量：属性指定在 Microsoft SQL Server 为插入的行生成标识值时，在现有的最大行标识值基础上所加的值。标识增量必须是 非零 整数，位数等于或小于 10。）可使用函数IDENT_INCR ,用法：
SELECT IDENT_INCR('表名')4、待续