Find Table Usage

Question

Find Table Usage

kgayda

SSCrazy

Points: 2157
More actions
October 5, 2007 at 2:45 am

#198773

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

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

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

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

hha0 Valued Member Points: 58 More actions · Answer 1

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 SSChasing Mays Points: 650 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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.