偶也晕4了: SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','table',null,null,null)
请pbsql看看我写的对不对:(最后得到的表里有表名,表(视图)类型,注释)SELECT DISTINCT TABLE_NAME, TABLE_TYPE, COMMENTS FROM (SELECT T .TABLE_NAME, T .TABLE_TYPE, TT.COMMENTS FROM (SELECT NAME AS TABLE_NAME, XTYPE AS TABLE_TYPE, NULL AS COMMENTS FROM SYSOBJECTS) T, (SELECT OBJTYPE AS TABLE_TYPE, OBJNAME AS TABLE_NAME, VALUE AS COMMENTS FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, DEFAULT, DEFAULT) [ ::fn_listextendedproperty ]) TT WHERE T .TABLE_NAME = TT.TABLE_NAME UNION SELECT A.NAME AS TABLE_NAME, A.XTYPE AS TABLETYPE, NULL AS COMMENTS FROM SYSOBJECTS A WHERE XTYPE = 'V') B
不过,“SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','table',null,null,null) ”确实没列出视图来。
Y又改了下: SELECT DISTINCT TABLE_NAME, TABLE_TYPE, COMMENTS FROM (SELECT T .TABLE_NAME, TT.TABLE_TYPE, TT.COMMENTS FROM (SELECT NAME AS TABLE_NAME, XTYPE AS TABLE_TYPE, NULL AS COMMENTS FROM SYSOBJECTS) T, (SELECT OBJTYPE AS TABLE_TYPE, OBJNAME AS TABLE_NAME, VALUE AS COMMENTS FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, DEFAULT, DEFAULT) [ ::fn_listextendedproperty ]) TT WHERE T .TABLE_NAME = TT.TABLE_NAME UNION SELECT A.NAME AS TABLE_NAME, 'VIEW' AS TABLETYPE, NULL AS COMMENTS FROM SYSOBJECTS A WHERE XTYPE = 'V') B
建议你看看联机帮助fn_listextendedproperty: SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','table',null,null,null) SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','view',null,null,null) 不过偶还不知道怎么给视图加描述属性:D,所以没法测
谢谢pbsql。 这次是第一次这么快揭贴。以前都要等几天才行。^_^ !
刚才又试了下“SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','view',null,null,null)” ,没有效果诶。用我上面写的那句则可以显示数据库中的视图名来。
http://community.csdn.net/Expert/topic/3390/3390700.xml?temp=.8183252
SELECT value
FROM ::fn_listextendedproperty(NULL,'user','dbo','table','t',default,default)
我是想一次就把数据库中所有e文表名和对应中文名给获取到。
用pbsql的方法只能一个一个的获取。
不过还是先谢谢pbsql/期待更完全的解决方法。
SELECT *
FROM ::fn_listextendedproperty(NULL,'user','dbo','table',null,null,null)
FROM (SELECT T .TABLE_NAME, T .TABLE_TYPE, TT.COMMENTS
FROM (SELECT NAME AS TABLE_NAME, XTYPE AS TABLE_TYPE, NULL
AS COMMENTS
FROM SYSOBJECTS) T,
(SELECT OBJTYPE AS TABLE_TYPE, OBJNAME AS TABLE_NAME,
VALUE AS COMMENTS
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, DEFAULT,
DEFAULT) [ ::fn_listextendedproperty ]) TT
WHERE T .TABLE_NAME = TT.TABLE_NAME
UNION
SELECT A.NAME AS TABLE_NAME, A.XTYPE AS TABLETYPE, NULL
AS COMMENTS
FROM SYSOBJECTS A
WHERE XTYPE = 'V') B
FROM ::fn_listextendedproperty(NULL,'user','dbo','table',null,null,null)
”确实没列出视图来。
SELECT DISTINCT TABLE_NAME, TABLE_TYPE, COMMENTS
FROM (SELECT T .TABLE_NAME, TT.TABLE_TYPE, TT.COMMENTS
FROM (SELECT NAME AS TABLE_NAME, XTYPE AS TABLE_TYPE, NULL
AS COMMENTS
FROM SYSOBJECTS) T,
(SELECT OBJTYPE AS TABLE_TYPE, OBJNAME AS TABLE_NAME,
VALUE AS COMMENTS
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, DEFAULT,
DEFAULT) [ ::fn_listextendedproperty ]) TT
WHERE T .TABLE_NAME = TT.TABLE_NAME
UNION
SELECT A.NAME AS TABLE_NAME, 'VIEW' AS TABLETYPE, NULL
AS COMMENTS
FROM SYSOBJECTS A
WHERE XTYPE = 'V') B
SELECT *
FROM ::fn_listextendedproperty(NULL,'user','dbo','table',null,null,null)
SELECT *
FROM ::fn_listextendedproperty(NULL,'user','dbo','view',null,null,null)
不过偶还不知道怎么给视图加描述属性:D,所以没法测
这次是第一次这么快揭贴。以前都要等几天才行。^_^ !
FROM ::fn_listextendedproperty(NULL,'user','dbo','view',null,null,null)” ,没有效果诶。用我上面写的那句则可以显示数据库中的视图名来。