Post reply

Wildcard search for column names

  • July 11, 2009 at 12:20 pm

    #139803

    I have forgotten how to search for a column name through a certain DB.

    For instance I need to find all columns that have *title* in the name and the name of the tables that have them.

    Any help is appreciated

  • July 11, 2009 at 12:55 pm

    #1023543

    here is a simple procedure i use all the time;

    stick in the master database, and because it starts with "sp_" and uses the catibility views, it will be functional in all databases.

    so simple to use: sp_find title

    create procedure sp_find

    @findcolumn varchar(50)

    as

    begin

    set nocount on

    select

    sysobjects.name as TableFound,

    syscolumns.name as ColumnFound

    from sysobjects

    inner join syscolumns on sysobjects.id=syscolumns.id

    where syscolumns.name like '%' + @findcolumn +'%'

    or sysobjects.name like '%' + @findcolumn +'%'

    order by sysobjects.name

    end

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 11, 2009 at 1:06 pm

    #1023544

    Thanks Lowell!

  • July 11, 2009 at 6:59 pm

    #1023563

    Lowell (7/11/2009)

    here is a simple procedure i use all the time;

    stick in the master database, and because it starts with "sp_" and uses the catibility views, it will be functional in all databases.

    so simple to use: sp_find title

    or, updated for 2005...

    create procedure sp_find

    @findcolumn varchar(50)

    as

    begin

    set nocount on

    select

    sys.Tables.name as TableFound,

    sys.Columns.name as ColumnFound

    from sys.Tables

    inner join sys.Columns on sys.Tables.object_id=sys.Columns.object_id

    where sys.columns.name like '%' + @findcolumn +'%'

    or sys.Tables.name like '%' + @findcolumn +'%'

    order by sys.Tables.name

    end

    Nice code Lowell.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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