score:2
This script will search all tables and all string columns for a specific string. You might be able to adapt this for your needs:
DECLARE @tableName sysname
DECLARE @columnName sysname
DECLARE @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)
SET @value = 'EDUQ4' -- *** Set this to the value you're searching for *** --
SET @sqlPreamble = 'IF EXISTS (SELECT 1 FROM '
DECLARE theTableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT LIKE '%temp%' AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
ORDER BY TABLE_NAME
OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
DECLARE theColumnCursor CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
ORDER BY ORDINAL_POSITION
OPEN theColumnCursor
FETCH NEXT FROM theColumnCursor INTO @columnName
WHILE @@FETCH_STATUS = 0 -- spin through Column entries
BEGIN
SET @sql = @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @value +
''') PRINT ''Value found in Table: ' + @tableName + ', Column: ' + @columnName + ''''
EXEC (@sqlPreamble + @sql)
FETCH NEXT FROM theColumnCursor INTO @columnName
END
CLOSE theColumnCursor
DEALLOCATE theColumnCursor
FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor
Read More
- Selecting column names that have specified value
- Find SQL Server Tables that have two specified column names
- Selecting the Id's that have the same EmailAddress column value
- How to query for rows that have highest column value among rows that have same value for one of the columns
- Selecting rows within group based on field that can have a value or is null
- Select from SQL Server table where all records have a column value of a range specified
score:0
If you have access to the files are RegEx will be must faster than performing a generic search in SQL.
If you are forced to use SQL @pmbAustin's answer is the way to go. Be warned, it won't run quickly.
score:1
One option you have is to be a little creative using XML in SQL Server.
Turn a row at a time into XML using cross apply and query for the nodes that has a certain value in a second cross apply.
Finally you output the distinct list of node names.
declare @Value nvarchar(max)
set @Value= 'b'
select distinct T3.X.value('local-name(.)', 'nvarchar(128)') as ColName
from YourTable as T1
cross apply (select T1.* for xml path(''), type) as T2(X)
cross apply T2.X.nodes('*[text() = sql:variable("@Value")]') as T3(X)
More questions
- Filtering rows that have same value in column but different max date
- Selecting rows that have certain number of specific type of character in a column
- How to find a gap in a time ordered table where a given column does not have a certain value for a specified interval
- How to write SQL script to find rows in any tables of a SQL Server database that have a string column which value is equal to a keyword?
- SQL to find records by ID that have value of only zero in separate column
- Selecting MAX on column then MAX from column that is dependent on first value
- Select rows that have a unique value of one column for each unique value of another column
- In T-SQL, how can I guarantee that a column value is updated only after all other column values in the same UPDATE statement have been updated?
- SQL Count how many column that have specific value in specific user
- Select values in SQL that have a colum value in one row and not a specific other column value in another
- How to select rows that have min value from a column but has an if statement in SQL based on another Column?
- find pairs of rows that have same column value
- In SQL how do I select the latest date that does not have a zero value in another column
- Can I have a column in my table that is hash value of another column and is automatically computed?
- populating 1 column of rows that have values 0 using SQL window functions possibly with next max/prev min value of row index
- SQL: Find all records in Table A that do not have a value in Table B based on a column value in Table B
- Inserting lots of rows that only have a single value change into table with lots of column
- How to select all rows which have same value in some column
- Copy data to and from the same table and change the value of copied data in one column to a specified value
- SQL: How can I update a value on a column only if that value is null?
- How to know if all the cells have the same value in some column
- I have the same column in multiple tables, and want to update that column in all tables to a specific value. How can I do this?
- Selecting every nth row from SQL Server 2008 query result where table does not have row id column
- How to select only records that have number in column (SQL)
More questions with similar tag
- c# Performance slowdown while looping/iterating through a long process
- Set Transaction Isolation level serializable blocks others transactions?
- How can I add a timestamp column to my SQL Server table when I create it?
- Modifying a column type with data, without deleting the data
- SQL server uft8 connection
- Reporting Services - Convert 2008 Report Format to the 2005 Format
- Realy year datediff (no year less)
- Error message when inserting date into table
- Update statement gives wrong result with subquery
- How to get SQLCMD to output errors and warnings only
- How do you read a txt file (from SQLCMD) into Pandas DataFrame?
- select only columns with values not matching between two rows
- How to allow a view to read from multiple schemas
- MySQL: why could copying to tmp table be faster after optimizing table
- SQL Geography data type, how to use the gibberish it contains?
- How to add not null unique column to existing table
- GridView Page Index
- Warning: dbx: module 'mssql' not loaded
- MSSQL Query omitting specific row value?
- SQL row output like column
- SQL Server Exception
- Using stored procedure output parameters in C#
- Select Command initialized Error
- Condense Records in SQL Server
- How to merge changes from a source-controlled VS2010 database project to local development DB
- Log Table aggregation
- What is the practical use of FIRST_VALUE and LAST_VALUE of SQL Server 2012 (Code name: Denali) CTP 3
- Average Counts by Hour by Day of Week
- How to solve ISO_WEEK partitioning issues for transactions when bridging over two different years
- How to fix Incorrect Syntax near returns