sql server數(shù)據(jù)庫的列信息查看的方法是最常用的操作方法之一，本文為您詳述了怎么使用T-Sql語句， 在sql server 2005中提取一個數(shù)據(jù)表的列的詳細信息的方法，并附sql server的開發(fā)實例，供您參考，希望對您有所幫助。

下面介紹的是名為sp_GetColumnsInfo的存儲過程，示例中使用了微軟提供的AdventureWorks數(shù)據(jù)庫。

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#p#

      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 Server2005中跨庫執(zhí)行SQL語句的方法

SqlServer數(shù)據(jù)庫中的分頁語句

SQL Server中存儲過程慢于SQL語句直接執(zhí)行的原因

SQL SERVER數(shù)據(jù)庫中存儲過程使用循環(huán)語句

Sql Server兩個版本中顯示所有表信息的語句