如果想要顯示Sql Server數(shù)據(jù)庫中的有所表信息，應(yīng)該使用什么方法呢？下面將為您介紹sql server 2000 與 2005中顯示所有表信息的語句，供您參考。

顯示某個Sql Server某個數(shù)據(jù)庫中所有表或視圖的信息，sql server 2000 與 2005 不同差別在于紅色字部分，以下語句為獲取所有表信息，替換綠色黑體字"U"為"V"為獲取所有視圖信息。

Sql Server 2000 版本

SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sysproperties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
   LEFT OUTER JOIN sysproperties ON
   ( sysproperties.smallid = syscolumns.colid
     AND sysproperties.id = syscolumns.id)
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
   WHERE syscolumns.id IN 
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid

Sql Server 2005版本

SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
   LEFT OUTER JOIN sys.extended_properties ON
   ( sys.extended_properties.minor_id = syscolumns.colid
     AND sys.extended_properties.major_id = syscolumns.id)
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
   WHERE syscolumns.id IN 
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid

 【編輯推薦】

SQL Server中一個語句塊實現(xiàn)多條語句插入的方法

使用SQL語句查看SQL Server事務(wù)日志的方法

SQL語句中UPDATE的三種用法

如何解決SQL Server中傳入select語句in范圍參數(shù)

使用SQL中SELECT語句的使用條件邏輯