Using SP_MSForEachDB and SP_MSForEachTable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Using SP_MSForEachDB and SP_MSForEachTable -...

Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Wednesday, March 25, 2009 6:26 PM

SSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388

Comments posted to this topic are about the item Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #683787

dagenester

Posted Thursday, March 26, 2009 6:06 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, July 09, 2012 6:49 PM
Points: 23, Visits: 12

I JUST HAD a student ask me about how to loop through each database yesterday. PERFECT timing. :) Thanks for the awesome video. BigGrin

Post #684019

BC Featherstone

Posted Thursday, March 26, 2009 1:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 20, 2010 8:33 AM
Points: 9, Visits: 81

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

Post #684551

« Prev Topic | Next Topic »

Permissions