Looking for specific row data in all database tables? Without creating a procedure or table

Question

This is what I have so far found for all tables with over 100 rows:

SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) AND pa.rows >100 GROUP BY sc.name,ta.name,pa.rows ORDER BY TABLENAME 

Is there something similar when I can go through a database to find out the specific row data for a column in a table?

For example: Where c.name = GUITARS and GUTARS = 'Fender'

Edit: I do not have CREATE PROCEDURE OR CREATE TABLE permission

Just looking for any specific data under a specific column name, it doesn't matter if it returns a lot of rows.

Answer 1

See my answer on How to find a value anywhere in the SQL Server database , where I provide a script to search all tables in the database.

Pseudo-code description it will be select * from * where any like 'foo'

It also allows you to search for specific column names using standard like syntax, for example. %guitar% to search for column names that contain the word guitar.

It starts ad-hoc, so you do not need to create a stored procedure, but you need access to information_schema.

I use this script in SQL 2000 and almost every day in my database development work.

Answer 2

I don't know if my solutions work for you or not. But instead, I would use the following query to get all possible guitar tables in any combination.

Select t.name, c.name from sys.columns c inner join sys.tables t on c.object_id=t.object_id Where c.name like '%guitar%'

Suppose he gives 20-25 tables depending on the lack of tables and the use of guitar speakers. You can see the result set and you can almost find out your tables used. Now find the Fenders in the list of guessed objects.

I say that I am working on the maintenance of the erp application, and it has 6000+ tables and 13000+ procedures. So whenever I need to figure out related tables, I just use the same trick and it works.

Answer 3

This is a simple stored procedure that can search all the data in SQL Server database tables. It also has the ability to search in selected tables if table names are specified using comma separated values. It has the ability to generate SQL alone without executing SQL. Inclusion of the script version as well.

Parameters and use:

@Tablenames - specify the name of one table or the name of several tables with separated commas.

If left empty, it will check all tables in the database

@SearchStr - specify a search string. Use "%" for coin search.

EX: X% --- will give data starting with X

% X --- will give data ending in X

% X% --- will give data containing X

@GenerateSQLOnly - specify 1 if you want to generate SQL statements without searching the database.

By default, it is 0 and a search will be performed.

  IF OBJECT_ID('SP_SearchTables','P') IS NOT NULL DROP PROCEDURE SP_SearchTables GO CREATE PROCEDURE SP_SearchTables @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS SET NOCOUNT ON DECLARE @MatchFound BIT SELECT @MatchFound = 0 DECLARE @CheckTableNames Table ( Tablename sysname ) DECLARE @SQLTbl TABLE ( Tablename SYSNAME ,WHEREClause VARCHAR(MAX) ,SQLStatement VARCHAR(MAX) ,Execstatus BIT ) DECLARE @sql VARCHAR(MAX) DECLARE @tmpTblname sysname DECLARE @ErrMsg VARCHAR(100) IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') BEGIN INSERT INTO @CheckTableNames SELECT Name FROM sys.tables END ELSE BEGIN SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' INSERT INTO @CheckTableNames EXEC(@sql) END IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) BEGIN SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' PRINT @ErrMsg RETURN END INSERT INTO @SQLTbl ( Tablename,WHEREClause) SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id WHERE STY.name in ('varchar','char','nvarchar','nchar') AND SC.object_id = ST.object_id ORDER BY SC.name FOR XML PATH('') ) FROM SYS.tables ST JOIN @CheckTableNames chktbls ON chktbls.Tablename = ST.name JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id WHERE ST.name <> 'SearchTMP' GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME) ; UPDATE @SQLTbl SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) DELETE FROM @SQLTbl WHERE WHEREClause IS NULL WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) BEGIN SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0 IF @GenerateSQLOnly = 0 BEGIN IF OBJECT_ID('SearchTMP','U') IS NOT NULL DROP TABLE SearchTMP EXEC (@SQL) IF EXISTS(SELECT 1 FROM SearchTMP) BEGIN SELECT Tablename=@tmpTblname ,* FROM SearchTMP SELECT @MatchFound = 1 END END ELSE BEGIN PRINT REPLICATE('-',100) PRINT @tmpTblname PRINT REPLICATE('-',100) PRINT replace(@sql,'INTO SearchTMP','') END UPDATE @SQLTbl SET Execstatus = 1 WHERE Tablename = @tmpTblname END IF @MatchFound = 0 BEGIN SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' PRINT @ErrMsg RETURN END SET NOCOUNT OFF go 

Posted by Sorna Kumar Muthuraj

Something I use at work for a really flexible, useful piece of code.

If you do not have permission to create procs, just declare the variables used in this script and execute the script as an ad-hoc request

  DECLARE @Tablenames VARCHAR(500) = 'Table_Name' DECLARE @SearchStr NVARCHAR(60) = 'Data_LookingFor' DECLARE @GenerateSQLOnly Bit = 0 

Your database administrator really doesn’t trust you what he is doing :) Anyway, I changed the code a little more efficiently using the temp table and not the table variables, maybe this will work for you:

 DECLARE @Tablenames VARCHAR(500) = 'Table_name' DECLARE @SearchStr NVARCHAR(60) = 'Serach_String' DECLARE @GenerateSQLOnly Bit = 0 SET NOCOUNT ON DECLARE @MatchFound BIT SELECT @MatchFound = 0 IF OBJECT_ID('tempdb..#CheckTableNames') IS NOT NULL DROP TABLE #CheckTableNames CREATE Table #CheckTableNames ( Tablename sysname ) IF OBJECT_ID('tempdb..#SQLTbl') IS NOT NULL DROP TABLE #SQLTbl CREATE TABLE #SQLTbl ( Tablename SYSNAME ,WHEREClause VARCHAR(MAX) ,SQLStatement VARCHAR(MAX) ,Execstatus BIT ) DECLARE @sql VARCHAR(MAX) DECLARE @tmpTblname sysname DECLARE @ErrMsg VARCHAR(100) IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') BEGIN INSERT INTO #CheckTableNames SELECT Name FROM sys.tables END ELSE BEGIN SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' INSERT INTO #CheckTableNames EXEC(@sql) END IF NOT EXISTS(SELECT 1 FROM #CheckTableNames) BEGIN SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' PRINT @ErrMsg RETURN END INSERT INTO #SQLTbl ( Tablename,WHEREClause) SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id WHERE STY.name in ('varchar','char','nvarchar','nchar') AND SC.object_id = ST.object_id ORDER BY SC.name FOR XML PATH('') ) FROM SYS.tables ST JOIN #CheckTableNames chktbls ON chktbls.Tablename = ST.name JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id WHERE ST.name <> 'SearchTMP' GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME) ; UPDATE #SQLTbl SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) DELETE FROM #SQLTbl WHERE WHEREClause IS NULL WHILE EXISTS (SELECT 1 FROM #SQLTbl WHERE ISNULL(Execstatus ,0) = 0) BEGIN SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement FROM #SQLTbl WHERE ISNULL(Execstatus ,0) = 0 IF @GenerateSQLOnly = 0 BEGIN IF OBJECT_ID('SearchTMP','U') IS NOT NULL DROP TABLE SearchTMP EXEC (@SQL) IF EXISTS(SELECT 1 FROM SearchTMP) BEGIN SELECT Tablename = @tmpTblname,* FROM SearchTMP SELECT @MatchFound = 1 END END ELSE BEGIN PRINT REPLICATE('-',100) PRINT @tmpTblname PRINT REPLICATE('-',100) PRINT replace(@sql,'INTO SearchTMP','') END UPDATE #SQLTbl SET Execstatus = 1 WHERE Tablename = @tmpTblname END IF @MatchFound = 0 BEGIN SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' PRINT @ErrMsg RETURN END