Find Table Usage

  • kgayda

    SSCrazy

    Points: 2157

    Comments posted to this topic are about the item Find Table Usage


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • hha0

    Valued Member

    Points: 58

    This script didn't work if a tab was used before the tablename. I modified the like clause like this and it seemed to pick them up

    ON c.Text LIKE '%[ ,=' + CHAR(9) + ']' + t.TableName + '[ .,' + CHAR(9) + ']%'

  • Martin Hawley

    SSC-Addicted

    Points: 480

    Is there a way to get it to run if the table name is the final part of the code? E.g. if a view finishes with 'FROM tbl_test'. Realise you could just remove the trailing '[ .,' + CHAR(9) + ']%', but then it would pick up any views referencing tbl_test2

  • indeb

    SSC Enthusiast

    Points: 193

    The script is good but unfortunately didn't work for me for finding the existence of a table in Views. I don't know why you are using LIKE '%[ ,=]' + t.TableName + '[ .,]%' when the simple LIKE '%' + t.TableName + '%' will give you the desired result.

    I modified this SP as I said above and it is giving me the correct result.

  • kgayda

    SSCrazy

    Points: 2157

    The script was posted as is from a particular task that I wrote it for. I don't recall the specifics but I needed the like query written the way it was to return the correct results.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • hha0

    Valued Member

    Points: 58

    The additional clauses between the brackets are trying to ignore partial matches. e.g. if you were searching for customer then it wouldn't return customerid. In most cases you probably want to return partial matches too though since it can't hurt.

    indeb (10/9/2007)


    The script is good but unfortunately didn't work for me for finding the existence of a table in Views. I don't know why you are using LIKE '%[ ,=]' + t.TableName + '[ .,]%' when the simple LIKE '%' + t.TableName + '%' will give you the desired result.

    I modified this SP as I said above and it is giving me the correct result.

  • indeb

    SSC Enthusiast

    Points: 193

    That was clear. But in that case you can not find a table usage in the following conditions:

    1) If the table name is not flanked by square brackets

    2) If the table name is not prefixed with owner

    Ex: If inside the SP you have code like this

    SELECT COL1,COL2 FROM TABLE1

    Then the script will fail to recognize the table usage of TABLE1

    Moreover even by square bracketing we can not avoid wrong search when say a table name and a column name matches exactly and the column name has also been square bracketed in the SP.

    Another case is when a statement is commented inside the SP /view/function. Then also the script will return the SP name if the commented statement contains the searched table.

    Basically we can be 100% sure of the usage, only when we write some parser kind of program to parse it well.

    Anyway, this script will be very handy for many user if we know the above facts and judiciously use it.:)

    hha0 (10/9/2007)


    The additional clauses between the brackets are trying to ignore partial matches. e.g. if you were searching for customer then it wouldn't return customerid. In most cases you probably want to return partial matches too though since it can't hurt.

    indeb (10/9/2007)


    The script is good but unfortunately didn't work for me for finding the existence of a table in Views. I don't know why you are using LIKE '%[ ,=]' + t.TableName + '[ .,]%' when the simple LIKE '%' + t.TableName + '%' will give you the desired result.

    I modified this SP as I said above and it is giving me the correct result.

  • kgayda

    SSCrazy

    Points: 2157

    The script assumes that the table name is not using brackets. Basically, it was written for myself and I posted because I thought it would be useful to others. I never create tables that are owned by other than dbo nor do I use table names that should be bracketed. Those usage scenarios are not best practices. However, if you have table names that require spaces or are not dbo owned, you will have to slightly modify the script to suit your needs.


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • icetee77

    Grasshopper

    Points: 21

    Useful for me, Thanks Karen. But if you have the same table name on 2 different databases, it returns no results. You have to specify the the database name, and then the table name to get the actual results. for example, when you run it by TableName only, you get zero results, but when you run it by DBName..TableName, then you get all the tables and stored procs, etc.

Viewing 9 posts - 1 through 9 (of 9 total)

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