我想有一个包含两列的表,第一列是视图的名称,在第二列上,我想在视图上有一个注释行(我写了一个视图的描述作为在 CREATE 行之后写的注释。有没有办法做到这一点?
或者有没有其他方法可以在视图中编写描述并在表中显示所有视图及其描述行?
im considering like you comment your description like below:
create view mytest
as
/* my description */
select * from Address
Answer for your question:
select a.name, substring(text,charindex('/*',text,1),charindex('*/',text,1)-charindex('/*',text,1)+2) 'text'
from sys.objects a join syscomments b on a.object_id=b.id where
a.type='V'
使用Krishnaraj Gunasekar的方法和另一种观点:
CREATE VIEW [dbo].[0__View List] AS
/*@ Description of the View @*/
SELECT TOP 999999 T1.TABLE_NAME [VIEW NAME],
CASE WHEN charindex('/*@', T1.VIEW_DEFINITION,1) <> 0 and charindex('@*/', T1.VIEW_DEFINITION,1) <> 0 THEN substring(
T1.VIEW_DEFINITION,charindex('/*@', T1.VIEW_DEFINITION,1)+3,charindex('@*/', T1.VIEW_DEFINITION,1)-charindex('/*@', T1.VIEW_DEFINITION,1)-3) ELSE '' END 'DEFINITION'
FROM [INFORMATION_SCHEMA].[VIEWS] T1
// WHERE (T1.TABLE_NAME like ...)
ORDER BY T1.TABLE_NAME ASC