February 3, 2014 at 8:34 am
Hello
I'm trying to get all extended properties for each database in my server.
In the test machine with ten databases the query that i built it's relatively quick, but using in the production machine it takes to much time to get all the info... I'm using a temp table and the sp_MSforeachdb.
GO
IF OBJECT_ID(N'tempdb..#TempDBs') IS NOT NULL DROP TABLE #TempDBs;
GO
CREATE TABLE #TempDBs(DatabaseName NVARCHAR(250), ExtendedProp NVARCHAR(MAX));
GO
EXEC sp_MSforeachdb N'INSERT INTO #TempDBs (DatabaseName) SELECT ''?'''
GO
EXEC sp_MSforeachdb N'UPDATE #TempDBs SET ExtendedProp = DBs.[Valor] FROM (SELECT [DBName] = ''?'', [Extended] = CAST(name AS varchar), [Valor] = CAST(Value AS varchar) FROM [?].sys.extended_properties WHERE class=0) DBs INNER JOIN #TempDBs ON #TempDBs.DatabaseName = [DBName]'
GO
SELECT DatabaseName,ExtendedProp FROM #TempDBs
I'm looking for other way to do this, that doesn't take so much time... 🙂
The select from sys.databases it takes no time, so if i could join this with the extenteded properties, but i don't see how, unless i build evey query, with a cursor or with a loop...
SELECT * FROM sys.databases
SELECT * FROM <databaseName>.sys.extended_properties
Thanks
February 6, 2014 at 5:21 am
Made some tests and the loop version its equally slow...
So do anyone knows how I can speed up this?
Thanks
February 6, 2014 at 5:43 am
this is just a variation on what you posted, butI see the delay is the WHERE Clause, 18 seconds for a server with 62 databases
but i don't have very many extended properties WHERE class=0;
CREATE TABLE [dbo].[#TempDBs] (
[DBName] NVARCHAR(128) NULL,
[Extended] VARCHAR(30) NULL,
[Valor] VARCHAR(30) NULL)
select * from sys.databases
EXEC sp_MSforeachdb N'
USE [?];
INSERT INTO #TempDBs(DBName,Extended,Valor)
SELECT
[DBName] = DB_Name(),
[Extended] = CAST(name AS varchar),
[Valor] = CAST(Value AS varchar)
FROM sys.extended_properties;'
when i removed the WHERE clause and grabbed EVERYTHING, it was instantaneous over all 62 databases. it might be better to do that, and then filter from the temp table.
IF OBJECT_ID('[tempdb].[dbo].[#TempDBs]') IS NOT NULL
DROP TABLE [dbo].[#TempDBs]
GO
CREATE TABLE [dbo].[#TempDBs] (
[DBName] NVARCHAR(128) NULL,
[class] TINYINT NOT NULL,
[class_desc] NVARCHAR(60) NULL,
[major_id] INT NOT NULL,
[minor_id] INT NOT NULL,
[name] SYSNAME NOT NULL,
[value] SQL_VARIANT NULL)
select * from sys.databases
EXEC sp_MSforeachdb N'
USE [?];
INSERT INTO #TempDBs
SELECT
[DBName] = DB_Name(), * FROM sys.extended_properties;'
SELECT
DBName,
[Extended] = CAST(name AS varchar),
[Valor] = CAST(Value AS varchar)
FROM #TempDBs WHERE Class=0
Lowell
February 6, 2014 at 5:52 am
I wouldn't use foreachDB:
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#TempDBs') IS NOT NULL DROP TABLE #TempDBs;
CREATE TABLE #TempDBs(DatabaseName NVARCHAR(250), ExtendedProp NVARCHAR(MAX));
DECLARE @sql VARCHAR(MAX);
SET @sql = '';
SELECT @sql = @sql + 'INSERT #TempDBs SELECT d.name, CAST(Value AS varchar) FROM (SELECT ''' + name + ''' name) d left join ' + name + '.sys.extended_properties ep on ep.class=0; ' + CHAR(13) + CHAR(10)
from sys.databases where LEN(owner_sid)>1
EXEC (@sql)
SELECT * FROM #TempDBs
EDITED: Oops, forgot that it could be more than one extended property per db. Code amended...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply