Is there an easy way to determine which column is not in use?

  • 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

  • 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