Filter Extended Properties by Schema

Question

Post reply

Filter Extended Properties by Schema

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 5, 2012 at 12:13 pm

#266879

I need to filter the statement listed below that it only returns schema that I specify in the WHERE Clause.
For example WHERE Schema = 'CTL'
SELECT OBJECT_NAME(ep.major_id) AS [ObjectName],
CAST(Value AS nvarchar(500)) AS [MS_Description]
,OBJECT_SCHEMA_NAME(ep.major_id) as SchemaName
FROM sys.extended_properties AS ep
WHERE ep.name = N'MS_Description' AND ep.minor_id = 0
ORDER BY OBJECT_NAME(ep.major_id), OBJECT_SCHEMA_NAME(ep.major_id)
How can I revise the cord to return the intended result?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

looks like you already have it there, Welsh Corgi;

you are using the object_schema_name() function, so just add it to your WHERE staetment :

AND OBJECT_SCHEMA_NAME(ep.major_id) = 'CTL'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Lowell,

Hey yeah your right, thanks.

Whatever happened to your Dog Avatar? I liked it. What model is that Gas:-) Mask? I did not see that one before.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 3

Welsh Corgi (4/5/2012)
Lowell,
Hey yeah your right, thanks.
Whatever happened to your Dog Avatar? I liked it. What model is that Gas:-) Mask? I did not see that one before.

yeah my dog avatar is my mutt Debbie;

I kind of felt like changing it; this avatar is the droid police officer at the beginning of the new Star Wars movie, when he chases childhood Kirk in the stolen Corvette.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lowell (4/5/2012)
Welsh Corgi (4/5/2012)
Lowell,
Hey yeah your right, thanks.
Whatever happened to your Dog Avatar? I liked it. What model is that Gas:-) Mask? I did not see that one before.
yeah my dog avatar is my mutt Debbie;
I kind of felt like changing it; this avatar is the droid police officer at the beginning of the new Star Wars movie, when he chases childhood Kirk in the stolen Corvette.

Me thinks you mean Star Trek. 😉

CapnHector SSCoach Points: 16143 More actions · Answer 5

Lynn Pettis (4/5/2012)
Lowell (4/5/2012)
Welsh Corgi (4/5/2012)
Lowell,
Hey yeah your right, thanks.
Whatever happened to your Dog Avatar? I liked it. What model is that Gas:-) Mask? I did not see that one before.
yeah my dog avatar is my mutt Debbie;
I kind of felt like changing it; this avatar is the droid police officer at the beginning of the new Star Wars movie, when he chases childhood Kirk in the stolen Corvette.
Me thinks you mean Star Trek. 😉

star wars, star trek, whats the real difference

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]