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

• Ignas Chilewa

  Mr or Mrs. 500

  Points: 516

  More actions

  December 7, 2006 at 10:21 am

  #92048

  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

• Adi Cohen

  Old Hand

  Points: 330

  More actions

  December 8, 2006 at 2:51 am

  #676838

  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