How do I search an SQL Server database for a string

Question

I know it s possible  but I don t know how   I need to search an SQL nbsp Server database for all mentions of a specific string   For example  I would like to search all tables  views  functions  stored procedures      for string  tblEmployes   not data within the tables    One of the reasons I need this is I would like to remove some extra data tables that are created  but I am afraid that they are maybe used somewhere in procedures or functions

User · Answer

For getting a table by name in SQL nbsp Server   SELECT   FROM sys Tables WHERE name LIKE   Employees     For finding a stored procedure by name   SELECT name FROM sys objects WHERE name    spName    To get all stored procedures related to a table   ----Option 1 SELECT DISTINCT so name FROM syscomments sc INNER JOIN sysobjects so ON sc id so id WHERE sc TEXT LIKE   tablename   ----Option 2 SELECT DISTINCT o name  o xtype FROM syscomments c INNER JOIN sysobjects o ON c id o id WHERE c TEXT LIKE   tablename

User · Answer

You can export your database  if small  to your hard drive   desktop  and then just do a string search via a text search program or text editor

User · Answer

Here is how you can search the database in Swift using the FMDB library   First  go to this link and add this to your project  FMDB  When you have done that  then here is how you do it  For example  you have a table called Person  and you have firstName and secondName and you want to find data by first name  here is a code for that       func loadDataByfirstName firstName   String  completion   escaping CompletionHandler       if isDatabaseOpened           let query    select   from Person where firstName like    firstName            do               let results   try database executeQuery query  values   firstName               while results next                     let firstName   results string forColumn   firstName                         let lastName   results string forColumn   lastName                         let newPerson   Person firstName  firstName  lastName  lastName                  self persons append newPerson                            completion true           catch let err               completion false              print err localizedDescription                    database close             Then in your ViewController you will write this to find the person detail you are looking for     override func viewWillAppear   animated  Bool         super viewWillAppear animated        SQLManager instance openDatabase    success  in         if success               SQLManager instance loadDataByfirstName firstName   Hardi      success  in                 if success                          You have your data Here

User · Answer

This code searching procedure and function but not search in table    SELECT name  FROM   sys all objects  WHERE  Object definition object id   LIKE   text    ORDER BY name

User · Answer

If you need to find database objects  e g  tables  columns  and triggers  by name - have a look at the free Redgate Software tool called SQL nbsp Search which does this - it searches your entire database for any kind of string s        It s a great must-have tool for any DBA or database developer - did I already mention it s absolutely free to use for any kind of use

User · Answer

This will search for a string over every database   declare  search term varchar max  set  search term    something   select  search term    use   SET QUOTED IDENTIFIER ON select           db name           c name         b name       as  object       b type desc as  type       d obj def value         varchar max     as  definition  from       select distinct         a id     from sys syscomments a     where a  text  like       search term        a inner join sys all objects b     on b  object id    a id inner join sys schemas c     on c  schema id    b  schema id  cross apply       select          text      a1  text      from sys syscomments a1     where a1 id   a id     order by a1 colid     for xml path        type   d obj def  where c schema id not in  3 4  -- avoid searching in sys and INFORMATION SCHEMA schemas     and db id   not in  1 2 3 4  -- avoid sys databases   if object id  tempdb   textsearch   is not null drop table  textsearch create table  textsearch        object  varchar 300        type  varchar 300        definition  varchar max     insert  textsearch exec sp MSforeachdb  search term  select   from  textsearch order by  object

User · Answer

The content of all stored procedures  views and functions are stored in field text of table sysComments  The name of all objects are stored in table sysObjects and the columns are in sysColumns   Having this information  you can use this code to search in content of views  stored procedures  and functions for the specified word   Select b name from syscomments a inner join sysobjects b on a id   b id where text like   tblEmployes     This query will give you the objects which contains the word  tblEmployes     To search by the name of Objects you can use this code   Select name from sysobjects where name like    tblEmployes     And finally to find the objects having at least one column containing the word  tblEmployes   you can use this code   Select b name from syscolumns a inner join sysobjects b on a id   b id where a name like    tblEmployes     You can combine these three queries with union   Select distinct b name from syscomments a inner join sysobjects b on a id   b id where text like   tblEmployes   union Select distinct name from sysobjects where name like    tblEmployes   union Select distinct b name from syscolumns a inner join sysobjects b on a id   b id where a name like    tblEmployes     With this query you have all objects containing the word  tblEmployes  in content or name or as a column

User · Answer

Here is the same script as submitted by user l--      ---------              but corrected to work on a case-sensitive SQL instance  and with some other minor improvements   DROP PROCEDURE IF EXISTS dbo spFind Text In Database GO  CREATE PROCEDURE dbo spFind Text In Database      strText To Find NVARCHAR 4000        bitExact Match BIT   0 AS SET NOCOUNT ON  DECLARE  Temp TABLE RowId INT IDENTITY 1 1   SchemaName sysname  TableName sysname  ColumnName SysName  DataType VARCHAR 100   DataFound BIT       INSERT  INTO  Temp TableName SchemaName  ColumnName  DataType      SELECT  C TABLE NAME  C TABLE SCHEMA  C COLUMN NAME  C DATA TYPE     FROM    INFORMATION SCHEMA COLUMNS AS C             INNER Join INFORMATION SCHEMA TABLES AS T                 ON C TABLE NAME   T TABLE NAME         AND C TABLE SCHEMA   T TABLE SCHEMA     WHERE   TABLE TYPE    BASE TABLE              And DATA TYPE In   ntext   text   nvarchar   nchar   varchar   char     DECLARE  i INT DECLARE  MAX INT DECLARE  TableName sysname DECLARE  ColumnName sysname DECLARE  SchemaName sysname DECLARE  SQL NVARCHAR 4000  DECLARE  PARAMETERS NVARCHAR 4000  DECLARE  DataExists BIT DECLARE  SQLTemplate NVARCHAR 4000   SELECT   SQLTemplate   CASE WHEN  bitExact Match   1                             THEN  If Exists Select                                             From   ReplaceTableName                                           Where  Convert nVarChar 4000    ReplaceColumnName                                                                   strText To Find                                                                                        Set  DataExists   1                                  Else                                      Set  DataExists   0                              ELSE  If Exists Select                                             From   ReplaceTableName                                           Where  Convert nVarChar 4000    ReplaceColumnName                                                          Like         strText To Find                                                                                         Set  DataExists   1                                  Else                                      Set  DataExists   0                              END           PARAMETERS     DataExists Bit OUTPUT            i   1  SELECT  i   1   MAX   MAX RowId  FROM    Temp  WHILE  i  lt    MAX     BEGIN         SELECT   SQL   REPLACE REPLACE  SQLTemplate   ReplaceTableName   QUOTENAME SchemaName          QUOTENAME TableName     ReplaceColumnName   ColumnName          FROM     Temp         WHERE   RowId    i           PRINT  SQL         EXEC sp executesql  SQL   PARAMETERS   DataExists    DataExists OUTPUT          IF  DataExists  1             UPDATE  Temp SET DataFound   1 WHERE RowId    i          SET  i    i   1     END  SELECT  SchemaName TableName  ColumnName FROM     Temp WHERE   DataFound   1 GO

User · Answer

You could    Script the database to a single file and search the file for tblEmployees using a text editor  In SQL Server Management Studio  SSMS   right click over the database and choose Generate Scripts  Use SSMS  View Dependencies  by right clicking over tblEmployees to see which other objects are dependent on it Use a free third-party tool such as Redgate Software s SQL nbsp Search to search all database objects by name and content by keyword

User · Answer

Searching SQL Database objects is possible with SQL Server Management Studio  SSMS  with the following methods  with SSMS Object Search  object explorer details or T-SQL scripts as explained in following    Different ways to search for SQL nbsp Server database objects SQL Server Find Anything in Object Explorer in SSMS Search text with wildcards

User · Answer

This will search every column of every table in a specific database  Create the stored procedure on the database that you want to search in   The Ten Most Asked SQL Server Questions And Their Answers   CREATE PROCEDURE FindMyData String      DataToFind NVARCHAR 4000        ExactMatch BIT   0 AS SET NOCOUNT ON  DECLARE  Temp TABLE RowId INT IDENTITY 1 1   SchemaName sysname  TableName sysname  ColumnName SysName  DataType VARCHAR 100   DataFound BIT       INSERT  INTO  Temp TableName SchemaName  ColumnName  DataType      SELECT  C Table Name C TABLE SCHEMA  C Column Name  C Data Type     FROM    Information Schema Columns AS C             INNER Join Information Schema Tables AS T                 ON C Table Name   T Table Name         AND C TABLE SCHEMA   T TABLE SCHEMA     WHERE   Table Type    Base Table              And Data Type In   ntext   text   nvarchar   nchar   varchar   char     DECLARE  i INT DECLARE  MAX INT DECLARE  TableName sysname DECLARE  ColumnName sysname DECLARE  SchemaName sysname DECLARE  SQL NVARCHAR 4000  DECLARE  PARAMETERS NVARCHAR 4000  DECLARE  DataExists BIT DECLARE  SQLTemplate NVARCHAR 4000   SELECT   SQLTemplate   CASE WHEN  ExactMatch   1                             THEN  If Exists Select                                             From   ReplaceTableName                                           Where  Convert nVarChar 4000    ReplaceColumnName                                                                   DataToFind                                                                                        Set  DataExists   1                                  Else                                      Set  DataExists   0                              ELSE  If Exists Select                                             From   ReplaceTableName                                           Where  Convert nVarChar 4000    ReplaceColumnName                                                          Like         DataToFind                                                                                         Set  DataExists   1                                  Else                                      Set  DataExists   0                              END           PARAMETERS     DataExists Bit OUTPUT            i   1  SELECT  i   1   MAX   MAX RowId  FROM    Temp  WHILE  i  lt    MAX     BEGIN         SELECT   SQL   REPLACE REPLACE  SQLTemplate   ReplaceTableName   QUOTENAME SchemaName          QUOTENAME TableName     ReplaceColumnName   ColumnName          FROM     Temp         WHERE   RowId    i           PRINT  SQL         EXEC SP EXECUTESQL  SQL   PARAMETERS   DataExists    DataExists OUTPUT          IF  DataExists  1             UPDATE  Temp SET DataFound   1 WHERE RowId    i          SET  i    i   1     END  SELECT  SchemaName TableName  ColumnName FROM     Temp WHERE   DataFound   1 GO   To run it  just do this   exec FindMyData string  google   0   It works amazingly well

User · Answer

If I want to find where anything I want to search is  I use this   DECLARE  search string    varchar 200      SET  search string     myString        SELECT DISTINCT            o name AS Object Name             o type desc             m definition       FROM sys sql modules m            INNER JOIN            sys objects o              ON m object id   o object id      WHERE m definition Like  search string

User · Answer

You can also try ApexSQL Search     it   s a free SSMS add-in similar to SQL Search   If you really want to use only SQL you might want to try this script   select S name as  Schema   o name as  Object   o type desc as  Object Type   C text as  Object Definition  from sys all objects O inner join sys schemas S on O schema id   S schema id inner join sys syscomments C on O object id   C id where S schema id not in  3 4  -- avoid searching in sys and INFORMATION SCHEMA schemas and C text like   ICE    order by  Schema

User · Answer

I was given access to a database  but not the table where my query was being stored in   Inspired by  marc s answer  I had a look at HeidiSQL which is a Windows program that can deal with MySQL  SQL nbsp Server  and PostgreSQL   I found that it can also search a database for a string       It will search each table and give you how many times it found the string per table

User · Answer

My version     I named it  Needle in the haystack  for obvious reasons   It searches for a specific value in each row and each column  not for column names  etc   Execute search  replace values for the first two variables of course    DECLARE  SEARCH DB VARCHAR 100   REPLACE WITH YOUR DB NAME  DECLARE  SEARCH VALUE LIKE NVARCHAR 100  N  REPLACE WITH SEARCH STRING    SET NOCOUNT ON  DECLARE col cur CURSOR FOR SELECT TABLE CATALOG  TABLE SCHEMA  TABLE NAME  COLUMN NAME  DATA TYPE FROM information schema columns WHERE TABLE CATALOG  SEARCH DB AND DATA TYPE NOT IN   timestamp    datetime     DECLARE  TOTAL int    SELECT COUNT    FROM information schema columns WHERE TABLE CATALOG  SEARCH DB AND DATA TYPE NOT IN   timestamp    datetime       DECLARE  TABLE CATALOG nvarchar 500    TABLE SCHEMA nvarchar 500    TABLE NAME nvarchar 500    COLUMN NAME nvarchar 500    DATA TYPE nvarchar 500   DECLARE  SQL nvarchar 4000       PRINT  -------- BEGIN SEARCH --------   OPEN col cur   FETCH NEXT FROM col cur INTO  TABLE CATALOG   TABLE SCHEMA   TABLE NAME   COLUMN NAME   DATA TYPE   BEGIN TRY DROP TABLE   RESULTS  END TRY BEGIN CATCH END CATCH CREATE TABLE   RESULTS  TABLE CATALOG nvarchar 500   TABLE SCHEMA nvarchar 500   TABLE NAME nvarchar 500   COLUMN NAME nvarchar 500   DATA TYPE nvarchar 500   RECORDS int  DECLARE  SHOULD CAST bit 0 DECLARE  i int  0 DECLARE  progress sum bigint 0  WHILE   FETCH STATUS   0 BEGIN     -- PRINT      CAST  i as varchar 100      of     CAST  TOTAL as varchar 100             TABLE CATALOG      TABLE SCHEMA      TABLE NAME       COLUMN NAME       DATA TYPE           SET  SHOULD CAST    SELECT CASE  DATA TYPE                                 WHEN  varchar  THEN 0                                 WHEN  nvarchar  THEN 0                                 WHEN  char  THEN 0                                 ELSE 1 END       SET  SQL  SELECT      TABLE CATALOG     catalog name       TABLE SCHEMA     schema name       TABLE NAME     table name       COLUMN NAME     column name       DATA TYPE     data type                     COUNT     COLUMN NAME     records                   FROM    TABLE CATALOG      TABLE SCHEMA      TABLE NAME                  WHERE     CASE WHEN  SHOULD CAST 1 THEN  CAST     COLUMN NAME      as NVARCHAR max     ELSE       COLUMN NAME        END                LIKE       SEARCH VALUE LIKE              -- PRINT  SQL       IF  i   100   0         BEGIN             SET  progress sum    SELECT SUM RECORDS  FROM   RESULTS              PRINT CAST   i as varchar 100      of     CAST  TOTAL as varchar 100          CAST   progress sum as varchar 100           END      INSERT INTO   RESULTS  TABLE CATALOG  TABLE SCHEMA  TABLE NAME  COLUMN NAME  DATA TYPE  RECORDS      EXEC  SQL       FETCH NEXT FROM col cur INTO  TABLE CATALOG   TABLE SCHEMA   TABLE NAME   COLUMN NAME   DATA TYPE      SET  i  i 1     -- IF  i  gt  1000     --     BREAK END CLOSE col cur  DEALLOCATE col cur   SELECT   FROM   RESULTS WHERE RECORDS gt 0    Then to view results  even while executing  from another window  execute   DECLARE  SEARCH VALUE LIKE NVARCHAR 100  N   FLEX    SELECT   FROM   RESULTS WHERE RECORDS gt 0   SET NOCOUNT ON  DECLARE col cur CURSOR FOR SELECT TABLE CATALOG  TABLE SCHEMA  TABLE NAME  COLUMN NAME  DATA TYPE FROM   RESULTS WHERE RECORDS gt 0   DECLARE  TABLE CATALOG nvarchar 500    TABLE SCHEMA nvarchar 500    TABLE NAME nvarchar 500    COLUMN NAME nvarchar 500    DATA TYPE nvarchar 500   DECLARE  SQL nvarchar 4000       OPEN col cur   FETCH NEXT FROM col cur INTO  TABLE CATALOG   TABLE SCHEMA   TABLE NAME   COLUMN NAME   DATA TYPE  DECLARE  i int  0 DECLARE  SHOULD CAST bit 0  WHILE   FETCH STATUS   0 BEGIN     SET  SHOULD CAST    SELECT CASE  DATA TYPE                                 WHEN  varchar  THEN 0                                 WHEN  nvarchar  THEN 0                                 WHEN  char  THEN 0                                 ELSE 1 END       SET  SQL  SELECT      TABLE CATALOG     catalog name       TABLE SCHEMA     schema name       TABLE NAME     table name       COLUMN NAME     column name       DATA TYPE     data type                         COLUMN NAME                                         FROM    TABLE CATALOG      TABLE SCHEMA      TABLE NAME                  WHERE     CASE WHEN  SHOULD CAST 1 THEN  CAST     COLUMN NAME      as NVARCHAR max     ELSE       COLUMN NAME        END                LIKE       SEARCH VALUE LIKE              PRINT  SQL       EXEC  SQL       FETCH NEXT FROM col cur INTO  TABLE CATALOG   TABLE SCHEMA   TABLE NAME   COLUMN NAME   DATA TYPE      SET  i  i 1     -- IF  i  gt  10     --    BREAK END CLOSE col cur  DEALLOCATE col cur    Few mentions about it    it uses cursors instead of a blocking while loop it can print progress  uncomment if needed  it can exit after a few attempts  uncomment the IF at the end  it displays all records you can fine tune it as needed   DISCLAIMERS    DO NOT run it in production environments  It is slow  If the DB is accessed by other services users  please add   WITH  NOLOCK    after every table name in all the selects  especially the dynamic select ones  It does not validate protect against all sorts of SQL injection options  If your DB is huge  prepare yourself for some sleep  make sure the query will not be killed after a few minutes  It casts some values to string  including ints bigints smallints tinyints  If you don t need those  put them at the same exclusion lists with the timestamps at the top of the script

[sql-server] How do I search an SQL Server database for a string?

Examples related to sql-server

Examples related to function

Examples related to search

Examples related to stored-procedures