Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Table Usage


Find Table Usage

Author
Message
kgayda
kgayda
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 172
Comments posted to this topic are about the item Find Table Usage


Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com
hha0
hha0
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
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
Martin Hawley
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 144
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
indeb
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 173
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
kgayda
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 172
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
hha0
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
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
indeb
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 173
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.Smile





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
kgayda
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 172
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
icetee77
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search