Post reply

SCRIPT TO FIND A TABLENAME CONTAINING "XXXX" IN ALL DATABASES

  • December 5, 2005 at 9:50 am

    #95968

    I need to write a script which will find all instances of a table name containing the letters "XXXX" in al user databases on our production server.  Any help?

  • December 5, 2005 at 10:18 am

    #607742

    select name from sysobjects where xtype = 'U' and name like '%XXXX%'

  • December 5, 2005 at 10:19 am

    #607743

    Try using the sp_msforeachdb stored procedure and check the sysobjects table where name like 'XXXX%'

     

    Regards

     

    Carl

     

  • December 5, 2005 at 10:25 am

    #607745

    Try the following against the master database:

    create table #dbobjects (objectname varchar(500))

    exec sp_msforeachdb 'insert into #dbobjects

    select name as objectname

    from [?].dbo.sysobjects

    where name like ''%XXXX%'''

    select objectname  from #dbobjects

    drop table #dbobjects

     

    Regards

     

    Carl

  • December 5, 2005 at 10:28 am

    #607746

    Sorry I forgot to include the search for type = 'U' for users tables:

     

    create table #dbobjects (objectname varchar(500))

    exec sp_msforeachdb 'insert into #dbobjects

    select name as objectname

    from [?].dbo.sysobjects

    where name like ''%NewTable%''

    and type = ''U'''

    select objectname  from #dbobjects

    drop table #dbobjects

Viewing 5 posts - 1 through 4 (of 4 total)

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