Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video Expand / Collapse
Author
Message
Posted Wednesday, March 25, 2009 6:26 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
Comments posted to this topic are about the item Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #683787
Posted Thursday, March 26, 2009 6:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 9, 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.
Post #684019
Posted Thursday, March 26, 2009 1:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse