Getting extended properties from every database

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  February 3, 2014 at 8:34 am

  #289023

  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

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  February 6, 2014 at 5:21 am

  #1686539

  Made some tests and the loop version its equally slow...

  So do anyone knows how I can speed up this?

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 6, 2014 at 5:43 am

  #1686546

  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

  ---

  --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!

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 6, 2014 at 5:52 am

  #1686553

  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...

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]