1.取得資料庫中的資料表、欄位、型態、長度、等資訊語法
1: SELECT dbo.sysobjects.name AS sTableName,
2: dbo.syscolumns.name AS sColumnsName,
3: dbo.syscolumns.prec AS iColumnsLength,
4: dbo.syscolumns.colorder AS iColumnsOrder,
5: dbo.systypes.name + '' AS sColumnsType,
6: dbo.syscolumns.isnullable AS iIsNull
7: FROM dbo.sysobjects INNER JOIN
8: dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
9: dbo.systypes ON dbo.syscolumns.xusertype = dbo.systypes.xusertype
10: WHERE (dbo.sysobjects.xtype = 'U')
2.資料表中插入描述等擴展屬性
1: --创建表2: create table TestTable(a1 varchar(10),a2 char(2))
3: 4: --为表添加描述信息5: EXECUTE sp_addextendedproperty N'MS_Description', '人員資料表', N'user', N'dbo', N'table', N'表', NULL, NULL
6: 7: --为字段a1添加描述信息8: EXECUTE sp_addextendedproperty N'MS_Description', '姓名', N'user', N'dbo', N'table', N'表', N'column', N'a1'
9: 10: --为字段a2添加描述信息11: EXECUTE sp_addextendedproperty N'MS_Description', '性別', N'user', N'dbo', N'table', N'表', N'column', N'a2'
3.取得資料表中描述等擴展屬性
1: SELECT objname AS ColName, value AS Description
2: FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
3: '您的資料表名稱', 'column', null)
