Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Table Usage Expand / Collapse
Author
Message
Posted Friday, October 5, 2007 2:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168
Comments posted to this topic are about the item Find Table Usage


Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com
Post #407227
Posted Tuesday, October 9, 2007 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 16, 2014 9:29 PM
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) + ']%'
Post #408311
Posted Tuesday, October 9, 2007 4:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:24 AM
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
Post #408375
Posted Tuesday, October 9, 2007 11:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:43 AM
Points: 112, Visits: 114
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.
Post #408597
Posted Tuesday, October 9, 2007 1:49 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168
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
Post #408664
Posted Tuesday, October 9, 2007 5:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 16, 2014 9:29 PM
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.
Post #408763
Posted Thursday, October 11, 2007 10:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:43 AM
Points: 112, Visits: 114
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.
Post #409624
Posted Thursday, October 11, 2007 12:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168
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
Post #409676
Posted Tuesday, December 30, 2008 10:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 27, 2009 9:51 AM
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.
Post #627471
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse