SQL Server Performance

SP Finds specified field in all tables

Discussion in 'Contribute Your SQL Server Scripts' started by bradmcgehee, Jan 22, 2006.

  1. bradmcgehee New Member

    -- Leo Semenov, 2005. lsemenov@gmail.com<br />-- Generally useful SQL Server Stored Procedure: shows info about all User Tables having <br />-- specified field. It also tries to find similar field names.<br />-- Example: EXEC FindField dept_id<br />-- Remark: switch SQL Query Analyser to "Results in Text"<br /> <br />if exists (select * from sysobjects where id = object_id(N'FindField') and OBJECTPROPERTY(id, N'IsProcedure') = 1)<br />drop procedure [dbo].[FindField]<br />GO<br /> <br />CREATE PROCEDURE FindField <br /> @FieldName VARCHAR(100)<br />AS<br /> <br />SET NOCOUNT ON<br /> <br />DECLARE @TableName VARCHAR(1000)<br />DECLARE @ColumnName VARCHAR(1000)<br />DECLARE @SQL VARCHAR(8000)<br /> <br />PRINT '==========================================================================='<br />SELECT '=== Please switch SQL Query Analyser result pane to "Results in Text" ' AS [Attention !!!]<br />UNION<br />SELECT '=== Simply CTRL+F in Results Pane to find any field name!' AS [Attention !!!]<br />PRINT 'All tables having !!!exact!!! field "' + @FieldName + '"'<br />PRINT '==========================================================================='<br />PRINT ''<br />DECLARE curExactMatch CURSOR FOR<br /> SELECT t1.name as TableName, t2.name as ColumnName<br /> FROM syscolumns t2 INNER JOIN sysobjects t1 ON t1.id=t2.id<br /> WHERE t2.name = @FieldName AND t1.type = 'U'<br /> ORDER BY t2.name<br />OPEN curExactMatch<br />FETCH NEXT FROM curExactMatch INTO @TableName, @ColumnName<br />WHILE (@@FETCH_STATUS &lt;&gt; -1)<br />BEGIN<br /> SET @SQL = 'SELECT TOP 3 ' + @FieldName +', * FROM ' + @TableName <br /> PRINT @SQL<br /> EXEC (@SQL)<br /> FETCH NEXT FROM curExactMatch INTO @TableName, @ColumnName<br />END<br />CLOSE curExactMatch<br />DEALLOCATE curExactMatch<br />PRINT ''<img src='/community/emoticons/emotion-4.gif' alt=';P' />RINT ''<img src='/community/emoticons/emotion-4.gif' alt=';P' />RINT ''<img src='/community/emoticons/emotion-4.gif' alt=';P' />RINT ''<br />PRINT '==========================================================================='<br />PRINT 'All tables having field !!!like!!! "%' + @FieldName + '%"'<br />PRINT '==========================================================================='<br />PRINT ''<br />DECLARE curLikeMatch CURSOR FOR<br /> SELECT t1.name as TableName, t2.name as ColumnName<br /> FROM syscolumns t2 INNER JOIN sysobjects t1 ON t1.id=t2.id<br /> WHERE t2.name LIKE '%'+@FieldName+'%' AND t1.type = 'U' <br /> ORDER BY t2.name<br />OPEN curLikeMatch<br />FETCH NEXT FROM curLikeMatch INTO @TableName, @ColumnName<br />WHILE (@@FETCH_STATUS &lt;&gt; -1)<br />BEGIN<br /> SET @SQL = 'SELECT TOP 3 ' + @ColumnName +', * FROM ' + @TableName <br /> PRINT @SQL<br /> EXEC (@SQL)<br /> FETCH NEXT FROM curLikeMatch INTO @TableName, @ColumnName<br />END<br />CLOSE curLikeMatch<br />DEALLOCATE curLikeMatch<br />PRINT ''<img src='/community/emoticons/emotion-4.gif' alt=';P' />RINT ''<img src='/community/emoticons/emotion-4.gif' alt=';P' />RINT ''<img src='/community/emoticons/emotion-4.gif' alt=';P' />RINT ''<br />PRINT '==========================================================================='<br />PRINT 'All tables having field that !!!sounds!!! like "' + @FieldName + '"'<br />PRINT '==========================================================================='<br />PRINT ''<br />DECLARE curSoundsMatch CURSOR FOR<br /> SELECT TOP 3 t1.name as TableName, t2.name as ColumnName<br /> FROM syscolumns t2 INNER JOIN sysobjects t1 ON t1.id=t2.id<br /> WHERE t1.type = 'U' AND DIFFERENCE(t2.name, @FieldName) = 4<br /> ORDER BY DIFFERENCE(t2.name, @FieldName) DESC<br />OPEN curSoundsMatch<br />FETCH NEXT FROM curSoundsMatch INTO @TableName, @ColumnName<br />WHILE (@@FETCH_STATUS &lt;&gt; -1)<br />BEGIN<br /> SET @SQL = 'SELECT TOP 3 ' + @ColumnName +', * FROM ' + @TableName <br /> PRINT @SQL<br /> <br /> EXEC (@SQL)<br /> FETCH NEXT FROM curSoundsMatch INTO @TableName, @ColumnName<br />END<br />CLOSE curSoundsMatch<br />DEALLOCATE curSoundsMatch<br /> <br />SET NOCOUNT OFF<br />GO<br /> <br /><br />-- exec FindField 'password'<br />-- exec FindField 'paSword' -- even that will be found using DIFFERENCE()<br />
  2. Madhivanan Moderator

    Well. This Procedure is simple and avoid usage of Cursors and select top 3 rows


    if exists(select * from sysobjects where name='Get_Data_By_Finding_Column' and lower(xtype)='p')
    Drop procedure Get_Data_By_Finding_Column
    Go

    Create Procedure Get_Data_By_Finding_Column
    @ColName varchar(100)
    as

    Declare @t table(query varchar(1000),tables varchar(50))

    Insert into @t
    Select 'Select top 3 * from '+ table_name,table_name from Information_schema.columns T
    where column_name=@colName

    select * from @t

    Declare @sql varchar(8000)
    Select @sql=IsNull(@sql+' ','')+ query from @t
    exec(@sql)

    Now, run the procedure by

    EXEC Get_Data_By_Finding_Column 'yourColumn'

    Madhivanan

    Failing to plan is Planning to fail

Share This Page