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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply