Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video
-
Comments posted to this topic are about the item Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video
-
I JUST HAD a student ask me about how to loop through each database yesterday. PERFECT timing.
Thanks for the awesome video. 
-
And another thing:
If you have long database or table names be sure to put square brackets around the ? character, otherwise the name will be trucated and the pup will cack (technically speaking).
sp_MSforeachdb @command1 = 'use ?;' will NOT work for a long name
sp_MSforeachdb @command1 = 'use [?];' will work for a long name
For example, this will create an inventory of all of the assemblies loaded on a server where SharePoint is present in all of it's name-databases-with-a-guid-postfix glory:
declare @ListAssemblies bit
,@ListAssemblyReferences bit
select @ListAssemblies = 0
,@ListAssemblyReferences = 0
if @ListAssemblies = 1
begin
if object_id('tempdb..#AsmblyList') is not null drop table tempdb..#AsmblyList
create table #AsmblyList
(
ServerName varchar(20)
,DatabaseName sysname
,SqlServerObjectName sysname
,FilePathName nvarchar(260)
,ClrCharacteristics nvarchar(4000)
,AccessType nvarchar(60)
,IsVisible bit
,PrincipalId int
,AssemblyId int
,Added datetime
,Modified datetime
)
exec sp_MSforeachdb @command1 = 'use [?];
insert into #AsmblyList
select
cast(serverproperty(''servername'') as varchar(20)) ServerName
,db_name() DatabaseName
,A.name SqlAssemblyName
,F.name FileName
,A.clr_name ClrCharacteristics
,A.permission_set_desc AccessType
,A.is_visible IsVisible
,A.principal_id PrincipalId
,A.assembly_id AssemblyId
,A.create_date Added
,A.modify_date Modified
from
sys.assemblies A with (nolock)
inner join
sys.assembly_files F with (nolock)
on
A.assembly_id = F.assembly_id
order by
A.name
'
select * from #AsmblyList
if object_id('tempdb..#AsmblyList') is not null drop table tempdb..#AsmblyList
end
if @ListAssemblyReferences = 1
begin
if object_id('tempdb..#AsmblyUses') is not null drop table tempdb..#AsmblyUses
create table #AsmblyUses
(
ServerName varchar(20)
,DatabaseName sysname
,assembly_id int
,SqlAssemblyName sysname
,referenced_assembly_id int
,UsedSqlAssemblyName sysname
)
exec sp_MSforeachdb @command1 = 'use [?];
insert into #AsmblyUses
select
cast(serverproperty(''servername'') as varchar(20)) ServerName
,db_name()
,R.assembly_id
,A1.name SqlAssemblyName
,R.referenced_assembly_id
,A2.name UsedSqlAssemblyName
from
sys.assembly_references R with (nolock)
inner join
sys.assemblies A1 with (nolock)
on
R.assembly_id = A1.assembly_id
inner join
sys.assemblies A2 with (nolock)
on
R.referenced_assembly_id = A2.assembly_id
'
select * from #AsmblyUses
if object_id('tempdb..#AsmblyUses') is not null drop table tempdb..#AsmblyUses
end
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply