InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 21:14:07 GMT20http://www.sqlservercentral.com/Forums/Topic683787-29-1.aspxAnd 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 nameFor 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 = 0if @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 endif @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..#AsmblyUsesendThu, 26 Mar 2009 13:45:26 GMTBC Featherstonehttp://www.sqlservercentral.com/Forums/Topic683787-29-1.aspxI JUST HAD a student ask me about how to loop through each database yesterday. PERFECT timing. :) Thanks for the awesome video. :-DThu, 26 Mar 2009 06:06:44 GMTdagenesterhttp://www.sqlservercentral.com/Forums/Topic683787-29-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Video/66317/">Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video</A>[/B]Wed, 25 Mar 2009 18:26:36 GMTAndy Warren