stored procedure---finding all tables that contain a specific column

  • Hello, I am working on a stored procedure that will accept a column name and returns the name of the table or tables that have the column by that name. I am also coding a statement that calls the procedure. \

    Below is what I have so far but when I call the procedure I get the following error when using the column name VendorID:

    Incorrect syntax near 'VendorID'.

    --Creating stored procedure called sp_WhichTable

    use AP

    GO

    CREATE PROC sp_WhichTable

    AS

    SELECT o.name as [Table], c.name as [Column]

    FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id

    WHERE c.name like '%order%' AND o.xtype = 'u'

    ORDER BY 1

    --Calling the stored procedure

    Declare @ColumnName varchar

    exec sp_WhichTable @ColumnName = 'VendorID'

  • Well, your procedure (as you posted it) doesn't have any input parameters, hence trying to pass one is going to give an error.

    You don't need a variable called @ColumnName, you need to change the procedure so that it accepts an input parameter, of type varchar, called @ColumnName

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ok, I added an imput parameter and removed the variable. I am still getting this error message when calling the procedure- Incorrect syntax near 'VendorID'.

    CREATE PROC sp_WhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table], c.name as [Column]

    FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id

    WHERE c.name like '%order%' AND o.xtype = 'u'

    ORDER BY 1

    exec sp_WhichTable @ColumnName 'VendorID'

  • You left out the equals sign in the procedure call. You had that part right last time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • --The procedure was created ok:

    CREATE PROC sp_WhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table], c.name as [Column]

    FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id

    WHERE c.name like '%order%' AND o.xtype = 'u'

    ORDER BY 1

    --When I execute the sp :

    exec sp_WhichTable @ColumnName = 'invoiceID'

    I get 2 blank columns with the column headers TABLE and COLUMN. I expected to get some results.

    Looking at the tables I see that the column InvoiceID is in the InvoicesTable and InvoicesLineItems Tables.

  • You're passing the value invoiceid to the procedure, but you're doing nothing with it within the procedure.

    If you want to use the parameter to filter the query, it needs to be referenced within the query somehow. Perhaps in place of the hardcoded 'order'?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Two more things.

    Stored procedures should not be created with the sp_ name, That's for system procedures and means that SQL will look in one of the system databases first for the proc.

    Since you're on 2005, use sys.tables and sys.columns instead of sysobjects and syscolumns. They are just there for backward compatibility and will be removed in a future version.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • --The procedure was created ok:

    CREATE PROC sp_WhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table], c.name as [Column]

    FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id

    WHERE c.name like '%order%' AND o.xtype = 'u'

    ORDER BY 1

    --When I execute the sp :

    exec sp_WhichTable @ColumnName = 'invoiceID'

    I get 2 blank columns with the column headers TABLE and COLUMN. I expected to get some results.

    Looking at the tables I see that the column InvoiceID is in the InvoicesTable and InvoicesLineItems Tables.

  • I am now referencing the parameter in the stored procedure as follows:

    use master

    GO

    CREATE PROC spWhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table], c.name as [Column]

    FROM sys.tables o JOIN sys.columns c ON o.id = c.id

    WHERE c.name = '@ColumnName' AND o.xtype = 'u'

    I am receiving these errors:

    Msg 207, Level 16, State 1, Procedure spWhichTable, Line 7

    Invalid column name 'id'.

    Msg 207, Level 16, State 1, Procedure spWhichTable, Line 7

    Invalid column name 'id'.

    Msg 207, Level 16, State 1, Procedure spWhichTable, Line 8

    Invalid column name 'xtype'.

  • try it like this:

    CREATE PROC spWhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table]

    , c.name as [Column]

    FROM sys.tables o

    JOIN sys.columns c ON o.object_id = c.object_id

    WHERE c.name = '@ColumnName'

    AND o.type = 'U'

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (11/29/2008)


    try it like this:

    CREATE PROC spWhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table]

    , c.name as [Column]

    FROM sys.tables o

    JOIN sys.columns c ON o.object_id = c.object_id

    WHERE c.name = '@ColumnName'

    AND o.type = 'U'

    Almost, Barry... you left quotes around the variable...

    CREATE PROC spWhichTable

    @ColumnName varchar(50)

    AS

    SELECT o.name as [Table]

    , c.name as [Column]

    FROM sys.tables o

    JOIN sys.columns c ON o.object_id = c.object_id

    WHERE c.name = @ColumnName

    AND o.type = 'U'

    As Gail pointed out, the op changed the sp_ to just sp. I'd recommend not using any form of hungarian notation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/29/2008)


    Almost, Barry... you left quotes around the variable...

    Ahhrrr!!!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Heh... not to worry... it's not as bad as that dummy that thought the sum of the differences between two points could be used to determine the closest point., 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh. Well, thank goodness no one knows that we make mistakes. 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks for the help everyone.

Viewing 15 posts - 1 through 15 (of 24 total)

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