Search all tables, all columns for a specific value SQL Server [duplicate]

I’ve just updated my blog post to correct the error in the script that you were having Jeff, you can see the updated script here: Search all fields in SQL Server Database

As requested, here’s the script in case you want it but I’d recommend reviewing the blog post as I do update it from time to time

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'
 
 
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
 
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'

Related Posts:

  1. How to drop a table if it exists?
  2. SQL Server Installation – What is the Installation Media Folder?
  3. SQL-Server: The backup set holds a backup of a database other than the existing
  4. vs shell installation has failed with exit code 1638
  5. XOR in SQL Server
  6. What is the data type for Currency in SQL Server?
  7. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  8. What is an MDF file? [closed]
  9. What is the difference between varchar and nvarchar?
  10. What do Clustered and Non-Clustered index actually mean?
  11. Conversion failed when converting date and/or time from character string while inserting datetime
  12. Is there a Boolean data type in Microsoft SQL Server like there is in MySQL? [duplicate]
  13. SQL query to select dates between two dates
  14. SQL query to get the employee name and their manager name from the same table
  15. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  16. NOT IN vs NOT EXISTS
  17. Must declare the scalar variable
  18. T-SQL split string
  19. Function to Calculate Median in SQL Server
  20. Efficiently convert rows to columns in sql server
  21. Update multiple columns in SQL
  22. Nested select statement in SQL Server
  23. How do I escape a single quote in SQL Server?
  24. T-SQL split string based on delimiter
  25. SQL Query with SUM with Group By
  26. How to rename a table in SQL Server?
  27. How do I perform an IF…THEN in an SQL SELECT?
  28. Data source name not found, and no default driver specified
  29. SQL update from one Table to another based on a ID match
  30. How to drop all tables from a database with one SQL query?
  31. SQL Server : Arithmetic overflow error converting expression to data type int
  32. Optimistic vs. Pessimistic locking
  33. How to calculate percentage with a SQL statement
  34. Column name or number of supplied values does not match table definition
  35. Can a foreign key be NULL and/or duplicate?
  36. When to use SELECT … FOR UPDATE?
  37. Difference between JOIN and INNER JOIN
  38. The target principal name is incorrect. Cannot generate SSPI context
  39. Query comparing dates in SQL
  40. The EXECUTE permission was denied on the object ‘xxxxxxx’, database ‘zzzzzzz’, schema ‘dbo’
  41. BCP error “Unable to open BCP host data-file”
  42. “CASE” statement within “WHERE” clause in SQL Server 2008
  43. Query error with ambiguous column name in SQL
  44. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  45. How to find sum of multiple columns in a table in SQL Server 2005?
  46. Check if table exists in SQL Server
  47. Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint
  48. Keeping it simple and how to do multiple CTE in a query
  49. DateTime2 vs DateTime in SQL Server
  50. Difference between numeric, float and decimal in SQL Server
  51. CREATE TABLE IF NOT EXISTS equivalent in SQL Server
  52. SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904)
  53. How can I get column names from a table in SQL Server?
  54. SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
  55. What is the equivalent of ‘describe table’ in SQL Server?
  56. How do I query for all dates greater than a certain date in SQL Server?
  57. Add a column with a default value to an existing table in SQL Server
  58. Remote table-Valued Function Calls are not allowed
  59. How to create temp table using Create statement in SQL Server?
  60. MS SQL compare dates?
  61. ClassNotFoundException – com.microsoft.jdbc.sqlserver.SQLServerDriver
  62. The backend version is not supported to design database diagrams or tables
  63. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  64. The SELECT permission was denied on the object ‘Users’, database ‘XXX’, schema ‘dbo’
  65. How do you open an SDF file (SQL Server Compact Edition)?
  66. Get size of all tables in database
  67. Using RegEx in SQL Server
  68. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  69. Must declare the scalar variable
  70. Exclude a column using SELECT * [except columnA] FROM tableA?
  71. Fatal error: Call to undefined function sqlsrv_connect()
  72. MSSQL Error ‘The underlying provider failed on Open’
  73. PHP Fatal error: Call to undefined function mssql_connect()
  74. Trouble Connecting to sql server Login failed. “The login is from an untrusted domain and cannot be used with Windows authentication”
  75. Bulk load data conversion error (truncation)
  76. Difference between database and schema
  77. How to format a numeric column as phone number in SQL
  78. ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine.
  79. SQL Server Linked Server Example Query
  80. What datatype should be used for storing phone numbers in SQL Server 2005?
  81. How do I format a number with commas in T-SQL?
  82. SQL: IF clause within WHERE clause
  83. SQL datetime compare
  84. SQL Server dynamic PIVOT query?
  85. Incorrect syntax near ”
  86. How to write a foreach in SQL Server?
  87. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  88. To add server using sp_addlinkedserver
  89. Get everything after and before certain character in SQL Server
  90. Is there a Profiler equivalent for MySql?
  91. How to connect to a local database in SQL Server Management Studio?
  92. The server principal is not able to access the database under the current security context in SQL Server MS 2012
  93. Cannot drop database because it is currently in use
  94. What are named pipes?
  95. SQL Client for Mac OS X that works with MS SQL Server
  96. Comma separated results in SQL
  97. INSERT VALUES WHERE NOT EXISTS
  98. How to implement LIMIT with SQL Server? [duplicate]
  99. What is tableName.* in SQL
  100. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF

Leave a Comment