March 26, 2013 at 10:21 am
Someone please tell me what I'm doing wrong..... 🙁
All I want to do, is get a list of databases which don't have extended properties.
Query below:
exec ('create table #t_nodb(dbname varchar(120))
insert into #t_nodb (dbname)
exec sp_MSforeachdb ''if ''?'' in (''tempDB'') return
use ?
select ''?'' where ''?'' not in (select ''?'' from sys.extended_properties)''
select * from #t_nodb
drop table #t_nodb')
Thanks!
March 26, 2013 at 11:38 am
This should get you started.
DECLARE @strSQL VARCHAR(max)
DECLARE @dbaseName VARCHAR(255)
DECLARE proplist CURSOR local static forward_only read_only FOR
SELECT name
FROM sys.databases
WHERE database_id NOT IN ( 1, 2, 3, 4 )
OPEN proplist
FETCH next FROM proplist INTO @dbaseName
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'use [' + @dbaseName
+
']; if not exists (select * from sys.extended_properties) select '''
+ @dbaseName + ''''
EXEC (@strSQL)
FETCH next FROM proplist INTO @dbaseName
END
CLOSE proplist
DEALLOCATE proplist
March 26, 2013 at 12:53 pm
Since sp_msforeachdb and the good code posted by Erin both are cursors I figured I would take a shot at doing this without a cursor. This code should also work. It doesn't use a cursor and the results are all in a single table.
declare @MyQuery nvarchar(max) =
(
select 'select count(*) as PropertyCount, ''' + name + ''' as DBName from ' + name + '.sys.extended_properties union all '
from sys.databases
where database_id not in (1, 2, 3, 4)
for xml path('')
)
set @MyQuery = 'select DBName from (' + left(@MyQuery, len(@MyQuery) - 10) + ' )x where x.PropertyCount = 0 order by DBName'
exec sp_executesql @MyQuery
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 3:33 pm
Nicely done, Sean.
March 26, 2013 at 4:25 pm
sp_MSforeachdb is indeed a cursor, and not a particularly good one.
Still, it exists, and it's useful for quick-and-dirty tasks sometimes, particularly if you don't have a lot of dbs.
So, I'll offer a version for it:
CREATE TABLE #t_nodb (
dbname nvarchar(128)
)
EXEC sp_MSforeachdb '
IF ''?'' IN (''tempDB'') -- IN (''master'', ''model'', ''msdb'', ''tempdb'')
RETURN
INSERT INTO #t_nodb
SELECT ''?''
WHERE
NOT EXISTS(SELECT 1 FROM [?].sys.extended_properties)
'
SELECT *
FROM #t_nodb
ORDER BY dbname
DROP TABLE #t_nodb
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 1, 2013 at 6:31 am
Sean - That works, thank you very much.
OldHand, SSCertifiable and Ten Centuries, thanks for the tips.
April 1, 2013 at 7:45 am
seraph67 (4/1/2013)
Sean - That works, thank you very much.OldHand, SSCertifiable and Ten Centuries, thanks for the tips.
You're welcome. Glad that works for you.
BTW, you copied everybody's ranking and not their names. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2013 at 9:39 am
My bad =D
Again, thanks for the help, all. 🙂
FYI - Here's the final product, works well; just in case it can help someone else.
declare @SQL nvarchar(max)
select @SQL = 'declare @MyQuery nvarchar(max) =
(select ''select count(*) as PropertyCount, '''''' + name + '''''' as DBName from '' + name + ''.sys.extended_properties union all ''
from sys.databases
where name not in (''master'', ''tempDB'', ''model'',''msdb'', ''ReportServer'', ''ReportServerTempDB'', ''rx'')
for xml path(''''))
set @MyQuery = ''select DBName from ('' + left(@MyQuery, len(@MyQuery) - 10) + '' )x where x.PropertyCount = 0 order by DBName''
exec sp_executesql @MyQuery'
execute (@SQL)
April 1, 2013 at 10:33 am
ScottPletcher (3/26/2013)
sp_MSforeachdb is indeed a cursor, and not a particularly good one.Still, it exists, and it's useful for quick-and-dirty tasks sometimes, particularly if you don't have a lot of dbs.
...
And has the dubious honor of being an undocumented procedure as well.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply