July 20, 2004 at 9:38 am
Hey guys,
Is there any way to use a function like fn_listextendedproperty across databases? I know you can use system stored procedures like this: "exec MyDatabase.dbo.sp_systemstoredprocedure". I need to get the extended properties of a database that I am not in. "select * from ::fn_listextendedproperty(arglist)" only returns info for the database you are currently sitting in, and in the context I'll be using this, I'm not sitting in one. Any help? Thanks,
Joel
July 20, 2004 at 10:09 am
What about something like the following?
SELECT [name], '01/01/1900 00:00:00' Processed INTO #dbnames
FROM master..sysdatabases WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')
SELECT * FROM #dbnames
DECLARE @Loop INTEGER
DECLARE @i INTEGER
SET @Loop = (SELECT COUNT(*) FROM #dbnames WHERE Processed = '01/01/1900 00:00:00')
SET @i = 1
WHILE @i <= @Loop
BEGIN
DECLARE @DB NVARCHAR(255)
DECLARE @sql NVARCHAR(255)
SET @DB = (SELECT TOP 1 [name] FROM #dbnames WHERE Processed = '01/01/1900 00:00:00')
SET @sql = 'USE ' + @DB
SET @sql = @sql + ' ' + 'SELECT * FROM ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)'
EXEC sp_executesql @sql
UPDATE #dbnames SET Processed = GETDATE() WHERE [name] = @DB
SET @i = @i + 1
END
SELECT * FROM #dbnames
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 20, 2004 at 11:31 am
I didn't need a good bit of that (I know what variables I need to pass) and I didn't understand some of it (since I'm relatively new to tsql), but yeah, that concept is perfect for what I need. I took your executesql logic and made a sp out of it:
Use Master
GO
Create procedure sp_GetTableDescription
@DatabaseName varchar(50),
@TableName varchar(50)
as
DECLARE @sql nvarchar(500)
SET @sql = 'USE ' + @DataBaseName
SET @sql = @sql + ' ' + 'SELECT value FROM ::fn_listextendedproperty(NULL, ''user'', ''dbo'', ''table'', ''' + @TableName + ''', NULL, NULL);'
exec sp_executesql @sql
GO
Thanks for the help!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply