SqlServer获取指定表、视图结构
1、获取指定表、视图的所有字段属性
只要输⼊不同的表/视图名，就可以获取该表的所有字段名字、字段长度、字段类型、字段说明、字段是否允许为空、是否主键、是否⾃增长字段等。
USE [数据库名称]
GO
/****** Object:  StoredProcedure [dbo].[A_P_GetColumnStructureInfo]    Script Date: 11/19/2015 16:11:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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
lid = lid )
THEN 1
ELSE 0
END ,
systypes.name AS DataType ,
syscolumns.length AS N''DataLength'',
--ded_properties.value AS Mark ,
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, ''Scale''), 0) AS N''Pricision'' ,
, '''') 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 ( pe = pe
AND systypes.name <> ''_default_''
AND systypes.name <> ''sysname''
)
left join syscomments  on syscolumns.cdefault = syscomments.id
LEFT OUTER ded_properties ON ( ded_properties.major_id = syscolumns.id
AND minor_id = lid
)
WHERE  syscolumns.id = ( SELECT    id
FROM      sysobjects
WHERE    name = '''+@tableName+'''
)
ORDER lid; '
PRINT @sqlTemp;
------返回查询结果-----
EXEC sp_executesql @sqlTemp;
GO
isnull的用法
⼩注：
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter 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
lid = lid )
THEN 1
ELSE 0
END ,
systypes.name AS DataType ,
syscolumns.length AS N''DataLength'',
--ded_properties.value AS Mark ,
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, ''Scale''), 0) AS N''Pricision'' ,
, '''') 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
--              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 ( pe = pe
AND systypes.name <> ''_default_''
AND systypes.name <> ''sysname''
)
left join syscomments  on syscolumns.cdefault = syscomments.id
LEFT OUTER ded_properties ON ( ded_properties.major_id = syscolumns.id
AND minor_id = lid
)
WHERE  syscolumns.id = ( SELECT    id
FROM      sysobjects
WHERE    name = '''+@tableName+'''
)
ORDER lid; '
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、待续

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。