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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply