sql server中查看列详细信息的语句(1)

sql server数据库的列信息查看的 *** 是最常用的操作 *** 之一，本文为您详述了怎么使用T-Sql语句， 在sql server 2005中提取一个数据表的列的详细信息的 *** ，并附sql server的开发实例，供您参考，希望对您有所帮助。

下面介绍的是名为sp_GetColumnsInfo的存储过程，示例中使用了微软提供的AdventureWorks数据库。

USE AdventureWorks;

--Method I: Using System Store Procedure

EXEC SP_COLUMNS @table_name = N'Employee'

SELECT

[columns].NAME AS [Name],

[usertype].NAME AS [DataType],

ISNULL([basetype].NAME, N'') AS [SystemType],

CAST(CASE WHEN [basetype].NAME IN (N'nchar', N'nvarchar') AND [columns].max_length <> -1 THEN

[columns].max_length/2 ELSE [columns].max_length END AS INT) AS [Length],

CAST([columns].PREcisION AS INT) AS [NumericPrecision]

FROM

SYS.TABLES AS [table]

INNER JOIN SYS.ALL_COLUMNS AS [columns] ON [columns].OBJECT_ID=[table].OBJECT_ID

LEFT OUTER JOIN SYS.TYPES AS [usertype] ON [usertype].user_type_id = [columns].user_type_id

LEFT OUTER JOIN SYS.TYPES AS [basetype] ON [basetype].user_type_id = [columns].system_type_id and [basetype].user_type_id = [basetype].system_type_id

WHERE

([table].NAME=@tablename and SCHEMA_NAME([table].schema_id)=@schema)

ORDER BY

[columns].column_id ASC

END

EXEC sp_GetColumnsInfo  @schema = N'HumanResources', @tablename = N'Employee'

结果显示

(16 row(s) affected)

sql server数据库的列信息查看的 *** 是最常用的操作 *** 之一，本文为您详述了怎么使用T-Sql语句， 在sql server 2005中提取一个数据表的列的详细信息的 *** ，并附sql server的开发实例，供您参考，希望对您有所帮助。

下面介绍的是名为sp_GetColumnsInfo的存储过程，示例中使用了微软提供的AdventureWorks数据库。

USE AdventureWorks;

--Method I: Using System Store Procedure

EXEC SP_COLUMNS @table_name = N'Employee'

go

--Method II: Using T-Sql Statement

--If existed, delete the store procedure

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'sp_GetColumnsInfo'

)

DROP PROCEDURE [dbo].sp_GetColumnsInfo

GO

--create store procedure, two parameters schema name and table name

CREATE PROCEDURE sp_GetColumnsInfo

@schema NVARCHAR(100),

@tablename NVARCHAR(100)

AS

BEGIN

SELECT

[columns].NAME AS [Name],

[usertype].NAME AS [DataType],

ISNULL([basetype].NAME, N'') AS [SystemType],

CAST(CASE WHEN [basetype].NAME IN (N'nchar', N'nvarchar') AND [columns].max_length <> -1 THEN

[columns].max_length/2 ELSE [columns].max_length END AS INT) AS [Length],

CAST([columns].PRECISION AS INT) AS [NumericPrecision]

FROM

SYS.TABLES AS [table]

INNER JOIN SYS.ALL_COLUMNS AS [columns] ON [columns].OBJECT_ID=[table].OBJECT_ID

LEFT OUTER JOIN SYS.TYPES AS [usertype] ON [usertype].user_type_id = [columns].user_type_id

LEFT OUTER JOIN SYS.TYPES AS [basetype] ON [basetype].user_type_id = [columns].system_type_id and [basetype].user_type_id = [basetype].system_type_id

WHERE

([table].NAME=@tablename and SCHEMA_NAME([table].schema_id)=@schema)

ORDER BY

[columns].column_id ASC

END

EXEC sp_GetColumnsInfo  @schema = N'HumanResources', @tablename = N'Employee'

结果显示

(16 row(s) affected)