February 25, 2011 at 11:48 am
Hello All,
I am trying to retrieve all the values from the Extended Properties table where the name is "Description". I'm using the below select statement;
SELECT
tbl.Name,
ep.name,
ep.Value
FROM sys.tables AS TBL
LEFT OUTER JOIN sys.extended_properties EP
ON TBL.object_id = EP.major_id
WHERE ep.name = 'Description'
This works fine when I select the database, but I'd like a piece of code that will look at all the user databases on the database server. Ideally, I'd like to return the database, the table, and the value of every Extended Property whose name is "Description" into a temporary table.
February 25, 2011 at 11:52 am
You'll have to do that by cycling through the databases. You can either do that with a cursor that selects from sys.databases, or by using sp_MSForEachDB. The sp will include the system databases, so you'll probably be better off building your own cursor for this.
If you call the query dynamically, with the database name added as part of a three-part-name for the table, and have it insert into a temp table, you can then finish off by querying the temp table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2011 at 3:23 pm
Hi again,
I've gotten this far and can't seem to get the quoting right;
declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 ='exec sp_MSforeachtable ' +
'exec sp_MSforeachdb @command1=''SELECT
tbl.Name,
ep.name,
ep.Value
FROM sys.tables AS TBL
LEFT OUTER JOIN sys.extended_properties EP
ON TBL.object_id = EP.major_id
WHERE ep.name = ''Description'''
print @cmd1
exec sp_MSforeachdb @command1=@cmd1
Please help 🙂
February 28, 2011 at 7:07 am
Try this instead:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T (
ID INT IDENTITY
PRIMARY KEY,
TblName SQL_VARIANT,
PropName SQL_VARIANT,
PropVal SQL_VARIANT) ;
DECLARE @Cmd VARCHAR(1000) ;
SELECT @Cmd = 'insert into #T (TblName, PropName, PropVal)
SELECT
tbl.Name,
ep.name,
ep.Value
FROM [?].sys.tables AS TBL
LEFT OUTER JOIN [?].sys.extended_properties EP
ON TBL.object_id = EP.major_id
WHERE ep.name = ''Description''' ;
EXEC sp_msforeachdb
@Cmd ;
SELECT *
FROM #T ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 28, 2011 at 2:08 pm
Worked perfectly. Thank you so so much!!!:-D
March 1, 2011 at 6:43 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply