Sql Server 获取指定表、视图结构

原创 2015年11月19日 18:04:31

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
                                            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 = '''+@tableName+'''
                        )
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 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
                                            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 = '''+@tableName+'''
                        )
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、待续



版权声明:作者:jiankunking 出处:http://blog.csdn.net/jiankunking 本文版权归作者和CSDN共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。

SQL Server 2008 学习笔记(三)视图的创建与修改

2015.7.31晚上 视图可以将多个表中的列联接起来,使它们看起来象一个表,但这个表却不会占用你的存储空间。 视图还可以用作跨表及跨域,在两台数据库之间做桥梁链接的作用,毕竟完全开启数据库...
  • Dead_Rabbit6_0
  • Dead_Rabbit6_0
  • 2015年07月31日 21:01
  • 6456

SQL Server2008中通过SQL获取表结构

SQL Server2008中通过SQL获取表结构 新增数据用户,角色为public,映射到待获取表结构的数据库上,授与用户在该数据库上的身份为db_owner 执行如下SQL语句: sele...
  • ghlfllz
  • ghlfllz
  • 2015年03月19日 17:09
  • 1812

获取SQL SERVER2012的数据库表结构

SELECT  (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'字段序号', a.name N'字段名', ...
  • waterxcfg304
  • waterxcfg304
  • 2015年02月25日 17:09
  • 1842

SQL-使用视图

什么是视图?它们怎样工作?何时使用它们?如何利用视图简化执行的某些SQL操作?1. 使用视图的原因 A. 重用SQL语句。 B. 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知...
  • beauty_1991
  • beauty_1991
  • 2016年04月19日 16:40
  • 5631

Sql Server 2008 快速查询表结构视图

Create view [dbo].[VW_QueryTable] as --快速查看数据库表结构 --暂不支持带有abo的表名 --BY SHW SELECT obj.name as TableN...
  • FlyAurora
  • FlyAurora
  • 2015年03月14日 10:15
  • 1931

java 获取数据库所有表结构

摘自其他文章,文章链接忘记了,略作修改,以供学习查看
  • w3226327
  • w3226327
  • 2016年07月11日 16:04
  • 3253

KETTLE整库迁移方案(SQL server迁移至Mysql,迁移过程自动创建表结构)

这个是SQL server迁移到Mysql数据库的,整库迁移操作,自动建表结构(可以Oracle、Mysql、SQL Server之间互相转换),只要有两个保存好的本地数据库链接或者资源库中的也可以。...
  • m0_37213323
  • m0_37213323
  • 2017年05月19日 15:58
  • 1486

sql server 树状结构表中,获取指定节点的所有父节点路径

CREATE PROCEDURE [dbo].[A_P_GetParentIds] ( @IdValue NVARCHAR(36) ,-- 子节点值 @tableNam...
  • xunzaosiyecao
  • xunzaosiyecao
  • 2015年11月06日 14:51
  • 2440

SQL Server2008存储结构之基本系统视图(转)

--数据库实例的概要情况 SELECT * FROM SYS.SERVERS  WHERE SERVER_ID=0  --兼容性视图SELECT * FROM SYS.SYSSERVERS...
  • zhaowenzhong
  • zhaowenzhong
  • 2011年03月25日 10:45
  • 600

SQL Server 比较两个数据库的视图和存储过程结构差异

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]....
  • tuzhen007
  • tuzhen007
  • 2014年02月26日 11:28
  • 1009
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Sql Server 获取指定表、视图结构
举报原因:
原因补充:

(最多只允许输入30个字)