December 7, 2006 at 10:21 am
Hi!,
I was wondering if there is an easy way to determine all columns in the table which are currently not in use by any other database objects.
Thanks,
Ignas
Dublin, OH
December 8, 2006 at 2:51 am
This procedure will let you know whether an expression/name appears anywhere on your server. Just feed it the column name you need to find.
create Proc usp_Objects_Search
@I_String_To_Search nvarchar(4000),
@I_Include_Script bit = 0
AS
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @vcCommand AS VARCHAR(8000)
DECLARE @sDatabaseName AS SYSNAME
CREATE TABLE #Routines
(
RoutineID INT NOT NULL IDENTITY(1,1) ,
DatabaseName SYSNAME NOT NULL ,
OwnerName SYSNAME NOT NULL ,
ObjectName SYSNAME NOT NULL ,
TableName SYSNAME NULL ,
ObjectType VARCHAR(20) NOT NULL ,
ObjectID INT NOT NULL
)
CREATE TABLE #RoutineTexts
(
RoutineID INT NOT NULL ,
RowSequence SMALLINT NOT NULL ,
Code NVARCHAR(4000) NULL
)
DECLARE CRS_L_Databases CURSOR LOCAL FOR
SELECT
[Name]
FROM
master..sysdatabases
WHERE
[Name] NOT IN ('master' , 'model' , 'msdb' , 'tempdb' , 'distribution')
OPEN CRS_L_Databases
FETCH NEXT FROM CRS_L_Databases
INTO @sDatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vcCommand = 'INSERT INTO #Routines '
+ '('
+ 'DatabaseName ,'
+ 'OwnerName ,'
+ 'ObjectName ,'
+ 'TableName ,'
+ 'ObjectType ,'
+ 'ObjectID'
+ ') '
+ 'SELECT '
+ '''' + @sDatabaseName + ''','
+ 'Users.name ,'
+ 'Child.name ,'
+ 'Parent.name ,'
+ 'CASE Child.xtype '
+ 'WHEN ''FN'' THEN ''Function'' '
+ 'WHEN ''IF'' THEN ''Function'' '
+ 'WHEN ''TF'' THEN ''Function'' '
+ 'WHEN ''P'' THEN ''Stored Procedure'' '
+ 'WHEN ''TR'' THEN ''Trigger'' '
+ 'WHEN ''V'' THEN ''View'' '
+ 'END ,'
+ 'Child.id '
+ 'FROM ' + @sDatabaseName + '.dbo.sysusers AS Users '
+ 'INNER JOIN ' + @sDatabaseName + '.dbo.sysobjects AS Child '
+ 'ON Users.uid = Child.uid '
+ 'LEFT OUTER JOIN ' + @sDatabaseName + '.dbo.sysobjects AS Parent '
+ 'ON Child.parent_obj = Parent.id '
+ 'WHERE Child.xtype IN (''FN'' , ''IF'' , ''TF'' , ''P'' , ''TR'' , ''V'')'
+ 'AND EXISTS '
+ '('
+ 'SELECT 1 '
+ 'FROM ' + @sDatabaseName + '.dbo.syscomments AS Comments '
+ 'WHERE Comments.id = Child.id '
+ 'AND Comments.encrypted != 1 '
+ 'AND Comments.text LIKE ''%' + @I_String_To_Search + '%'''
+ ') '
+ 'INSERT INTO #RoutineTexts '
+ '('
+ 'RoutineID ,'
+ 'RowSequence ,'
+ 'Code'
+ ') '
+ 'SELECT '
+ '#Routines.RoutineID ,'
+ 'Comments.colid ,'
+ 'CASE '
+ 'WHEN Comments.encrypted = 1 THEN NULL '
+ 'ELSE Comments.text '
+ 'END '
+ 'FROM #Routines '
+ 'INNER JOIN ' + @sDatabaseName + '.dbo.syscomments AS Comments '
+ 'ON #Routines.ObjectID = Comments.id '
+ 'WHERE #Routines.DatabaseName = ''' + @sDatabaseName + ''''
EXECUTE (@vcCommand)
FETCH NEXT FROM CRS_L_Databases
INTO @sDatabaseName
END
CLOSE CRS_L_Databases
DEALLOCATE CRS_L_Databases
SELECT @@ServerName Servername ,
DatabaseName ,
OwnerName ,
ObjectName ,
TableName ,
ObjectType ,
RowSequence ,
CASE WHEN (@I_Include_Script = 1) THEN CAST(Code AS VARCHAR(4000))
ELSE 'Not requested'
END AS Object_Script
FROM
#RoutineTexts
INNER JOIN
#Routines
ON
#RoutineTexts.RoutineID = #Routines.RoutineID
ORDER BY
DatabaseName ,
OwnerName ,
ObjectName ,
RowSequence
DROP TABLE #Routines
DROP TABLE #RoutineTexts
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply