Using SP_MSForEachDB and SP_MSForEachTable - SQL School Video

  • I JUST HAD a student ask me about how to loop through each database yesterday. PERFECT timing. 🙂 Thanks for the awesome video. 😀

  • 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