April 5, 2012 at 3:08 pm
Related to the last post I made about SPARSE columns, I've identified about 100 columns that are not used at all and we want to get rid of them. The problem is that we don't know if they are being used anywhere. The database has hundreds of stored procedures so looking through them isn't optimal.
Is there a better way to determine if a column is used in a select, join or where clause?
I was hoping to maybe query execution plans. I'm already querying against procs and funcs but going through each one is time consuming.
April 5, 2012 at 5:44 pm
I did NOT create this SP (Refer to the header for the author). But in testing it in my Sandbox DB it appeared to do what you desire to do.
So good luck and if you modify it to make it even wider in scope, or more robust, or whatever, please, please post your work here so that
others may benefit from Pat Reddy's work and yours.
CREATE PROC [dbo].[spUtil_Occur]
(
@SearchString VARCHAR(200) /* @SearchString SQL_VARIANT for SQL 2005 */
)
As
/****************************************************************************
*Procedure Name: spUtil_Occurences
*Author:????????Pat Reddy
*Purpose:????This procedure is useful for finding field and/or table references.
????????????????It will search the entire database. Best of all, if you create a key stroke shortcut
????????????????to execute the proc, you can highlight any text in your editor and search for it instantly.
????????????????And because the proc's only parameter is of type SQL_VARIANT, there's no need to
????????????????surround the input parameter with single quotes!
????????????????
????????????????Feel free to contact me with any questions at all:
????????????????Pat Reddy - Reddy Software Solutions at:????????reddys@charter.net
*By the way, I cannot take full credit for this script as I found it somewhere
years ago and merely modified it to my liking.
******************************************************************************/
SET NOCOUNT ON
DECLARE @SQL VARCHAR(1500)
SELECT @SQL = 'SELECT
SUBSTRING(SO.name, 1, 40 ) as Object,
COUNT(*) as Occurences, ' +
'CASE ' +
' WHEN SO.xtype = ''D'' THEN ''Default'' ' +
' WHEN SO.xtype = ''F'' THEN ''Foreign Key'' ' +
' WHEN SO.xtype = ''P'' THEN ''Stored Procedure'' ' +
' WHEN SO.xtype = ''PK'' THEN ''Primary Key'' ' +
' WHEN SO.xtype = ''S'' THEN ''System Table'' ' +
' WHEN SO.xtype = ''TR'' THEN ''Trigger'' ' +
' WHEN SO.xtype = ''V'' THEN ''View'' ' +
'END AS TYPE ' +
'FROM dbo.syscomments as SC
JOIN dbo.sysobjects as SO ON SC.id = SO.id ' +
'WHERE PATINDEX(''%' + CAST(@SearchString AS VARCHAR(100)) + '%'', SC.text ) > 0 ' +
'GROUP BY SO.name, SO.xtype ' +
'UNION ' +
'SELECT
SUBSTRING(SO.name, 1, 40 ) as Object,
1 as Occurances,
''User Table'' as TYPE
FROM sysobjects as SO
INNER JOIN syscolumns as SC on SC.id = SO.id
WHERE SC.name LIKE ''' + CAST(@SearchString AS VARCHAR(100)) + ''' AND SO.xtype =' + '''U'''
EXECUTE( @SQL )
RETURN
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply