SQL Server中與Metadata相關的預設table

SQL Server 2000中(之後可以改用VIEW來完成相同的工作)

有一些系統的table來儲放資料的metadata

System Tables in Every Database

  store database-level system information for each database.

syscolumns	Sysindexkeys
syscomments	sysmembers
sysconstraints	sysobjects
sysdepends	syspermissions
sysfilegroups	sysprotects
sysfiles	sysreferences
sysforeignkeys	systypes
sysfulltextcatalogs	sysusers
sysindexes

查詢某TABLE的某COLUMN存不存在


use [DB_NAME]
GO
DECLARE @tb_name VARCHAR(512), @col_name VARCHAR(512)
SET @tb_name = 'TABLE_NAME'
SET @col_name = 'COL_NAME'

IF NOT EXISTS(
SELECT 1
from sys.sysobjects so
INNER JOIN sys.syscolumns sc ON sc.id = so.id
where so.name = @tb_name AND sc.name = @col_name
)
BEGIN
PRINT 'COLUMN ''' + @col_name+ '''  is NOT EXISTS!!'
END
ELSE
BEGIN
PRINT 'COLUMN  ''' + @col_name+ '''  is  EXISTS!!'
END
GO

http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80)