18 lines
879 B
Transact-SQL
18 lines
879 B
Transact-SQL
declare @dbNames nvarchar(max)
|
|
set @dbnames = 'VEPROMS_%'
|
|
declare @CommandLine nvarchar(max)
|
|
set quoted_identifier off -- Allows single quotes to be used in the query'
|
|
set @CommandLine = "
|
|
Select
|
|
sum(case when type = 0 then 1 else 0 end) Procedures
|
|
,sum(case when type between 10000 and 19999 then 1 else 0 end) Sections
|
|
,sum(case when type >= 20000 then 1 else 0 end) Steps
|
|
from ~Contents
|
|
"
|
|
set quoted_identifier on
|
|
declare @CommandLines nvarchar(max)
|
|
select @CommandLines = Coalesce(@CommandLines + ' UNION' + char(10), '') + 'select ''' + Name + ''' DBName, * from ( ' + Replace(Replace(@CommandLine,'~','{Name}.dbo.'),'{Name}',Name) + ') T1'
|
|
from Sys.Databases where name like @dbNames and name not like '%test'
|
|
Set @CommandLines = 'Select * from ( ' +@CommandLines + ') t2 '--where HowMany > 0'
|
|
print @CommandLines
|
|
EXEC SP_EXECUTESQL @CommandLines |