trigger extended properties

[AccessSQLUser]

I need to get a list of all triggers in my database that have extended properties. The following statement works great but only for a specific table

Select *
FROM ::fn_listextendedproperty(null, 'user','dbo', 'table','tablename','trigger',default)

Instead of the table name, I tried putting default or null and that doesn't seem to work. I really don't want to have to loop through all the tables to do this.

 

[tlbroadbent]

Try the undocumented stored procedure sp_MSforeachtable.

EXEC sp_MSforeachtable 'Print ''?'' Select * FROM ::fn_listextendedproperty(null, ''user'',''dbo'', ''table'',''?'',''trigger'',default)' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[AccessSQLUser]

It doesn't seem to be working. I don't get any errors but the trigger that I have an extended property for is not appearing? Can you explain to me please how this stored procedure works? What does the 'Print ''?'' do?

 

[tlbroadbent]

Sorry to have lead you down this path. It appears that sp_MSforeachtable and associated SP's process the 'owner.table' in the place of the '?' while fn_listextendedproperty expects only the table name.

Print ''?'' should print the name of the table. NOTE: It prints the owner qualified name. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[AccessSQLUser]

So does that mean that I can't use this stored procedure for the purpose that I need it for? Also my final goal is to put all those triggers that have extended properties in one recordset - that's why if I could get the function fn_listextendedproperty to work without putting in the table name, it would be great.

 

[tlbroadbent]

One solution is to query the system tables directly. Try this query.

Select
o.Type,
TableName=cast(t.name as varchar(40)),
TriggerName=cast(o.name as varchar(40)),
PropertyName=cast(p.name as varchar(40)),
PropertyValue=p.Value

From sysproperties p
Inner Join sysobjects o on p.id=o.id
Inner Join sysobjects t on o.parent_obj=t.id

Where o.type='TR' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.