March 25, 2009 at 6:26 pm
Comments posted to this topic are about the item Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video
March 26, 2009 at 6:06 am
I JUST HAD a student ask me about how to loop through each database yesterday. PERFECT timing. Thanks for the awesome video.
March 26, 2009 at 1:45 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy