Sql Server 獲取指定表、檢視結構

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、待續