How to search a specific STRING in all TABLES in current DATABASE ?

Do you want to search a specific string in current database ? (In all the tables)

/*Creating a new Database*/
If DB_ID('SQLBuddy') Is Null
Create Database SQLBuddy
Go

/*Changing the Database context to "SQLBuddy" Database*/
Use SQLBuddy
Go

/*Creating Sample Tables: "Table1", "Table2", "Table3", "Table4"*/
If OBJECT_ID('Table1') Is Null
Create Table Table1
(
Id Int Identity(1,1),
Column1 Varchar(50),
Column2 NVarchar(50),
Column3 DateTime
)
Go

If OBJECT_ID('Table2') Is Null
Create Table Table2
(
Id Int Identity(1,1),
Column1 Int,
Column2 DateTime,
Column3 Char(50)
)
Go

If OBJECT_ID('Table3') Is Null
Create Table Table3
(
Id Int Identity(1,1),
Column1 Int,
Column2 DateTime
)
Go

If OBJECT_ID('Table4') Is Null
Create Table Table4
(
Id Int Identity(1,1),
Column1 NVarchar(100),
Column2 Varchar(50)
)
Go

/*Inserting Sample Data*/
Insert Table1(Column1, Column2, Column3) Values('MS SQL Server','SQL Administration',GETDATE())
Insert Table1(Column1, Column2, Column3) Values('MS SQL Server','SQL Developement',GETDATE())
Insert Table1(Column1, Column2, Column3) Values('Oracle','Oracle Administration',GETDATE())
Go

Insert Table2(Column1, Column2, Column3) Values(1,getdate(),'MS SQL')
Insert Table2(Column1, Column2, Column3) Values(2,getdate(),'MS Access')
Go

Insert Table3(Column1, Column2) Values(1,getdate())
Insert Table3(Column1, Column2) Values(2,getdate())
Go
 
Insert Table4(Column1, Column2) Values('MS SQL Server','BI')
Insert Table4(Column1, Column2) Values('MS SQL Server','Developement')
Go

/*Creating a stored procedure to findout the Matching Data from tables in current Database*/
If OBJECT_ID('Usp_Isavailable') Is Not null
Drop Proc Usp_Isavailable
Go
Create Proc Usp_Isavailable
(
@Table Varchar(100),
@Column Varchar(100),
@Search Varchar(1000),
@Available Varchar(100) Output
)
As
Begin
Set Nocount On

Declare @Query Varchar(1000)
Declare @Status Table(Available Varchar(100))

Select @Query = 'Select ' + @Column + ' from ' + @Table + ' where ' + @Column + ' like ''%' + @Search + '%'''

Insert @Status
Exec(@Query)

Select @Available = Available from @Status
End
Go
 
/*Creating stored procedure to identify/fetch the matching Columns & Tables in current Database*/
If Object_Id('Usp_FindString') Is Not Null
Drop Proc Usp_FindString
Go
Create Proc Usp_FindString
(
@Database Varchar(100) = NULL,
@TableName Varchar(100) = NULL,
@ColumnName Varchar(100) = NULL,
@SearchString Varchar(100)
)As
Begin
Set Nocount On

Create Table #Table_Column(TableName Varchar(100),ColumnName Varchar(100))
Create Table #Columns(TableSlno Int,ColumnSlno Int, TableName Varchar(100),ColumnName Varchar(100))
Create Table #Status(DatabaseName Varchar(100), TableName Varchar(100), ColumnName Varchar(100), MatchingValue Varchar(4000))

Insert #Table_Column
Select distinct Object_name(c.id) [Table], c.name [Column] from sys.syscolumns c join sys.types t on (c.xtype = t.system_type_id)
where t.collation_name Is Not null
and c.id >100
and Objectproperty(c.id,'IsUserTable') = 1
And Object_name(c.id) = ISNULL(@TableName, Object_name(c.id))
And c.name = ISNULL(@ColumnName, c.name)
Order by 1

Declare @Tables Int, @Seq Int, @Columns Varchar(4000), @Table Varchar(4000), @Script Varchar(MAX),@IsAvailable Varchar(100), @DatabaseName Varchar(100), @ColSeq Int, @ColTotal Int

;With CTEs
As
(
Select distinct o.TableName,o.ColumnName from #Table_Column o
)

Insert #Columns
Select Dense_Rank() Over(order by TableName),Dense_Rank() Over(Partition by TableName Order by ColumnName),TableName,ColumnName from CTEs

Select @Tables = Max(TableSlno) From #Columns
Select @Seq =1, @Columns ='', @Script ='', @DatabaseName = DB_Name(), @ColSeq = 1

While (@Seq <=@Tables)
Begin
Select @ColTotal = Max(ColumnSlno) From #Columns where TableSlno = @Seq
Select @ColSeq = 1

While(@ColSeq <= @ColTotal)
Begin
select @Table = TableName, @Columns = ColumnName from #Columns where TableSlno = @Seq And ColumnSlno = @ColSeq

EXEC Usp_Isavailable @Table,@Columns,@SearchString,@IsAvailable Output

If (@IsAvailable Is Not null AND @IsAvailable <>'')
Insert #Status Values(@DatabaseName,@Table,@Columns,@IsAvailable)

Select @IsAvailable = NULL
Select @ColSeq = @ColSeq + 1
End

Select @Seq = @Seq + 1
End

Select * from #Status

Drop Table #Table_Column
Drop Table #Columns
Drop Table #Status
End
Go
 
/*1. Searching a specific data("SQL") in all the tables & columns in current database*/
Exec Usp_FindString @SearchString = 'SQL'
Go
 
 
 
 
 
 
 
/*2. Searching a specific data("SQL") in all the tables in specific columns("Column1") in current database*/
Exec Usp_FindString @ColumnName ='Column1', @SearchString = 'SQL'
Go
 
 
 
 
 
/*3. Searching a specific data("SQL") in specific table("Table1") in specific columns("Column1") in current database*/
Exec Usp_FindString @TableName='Table1',@SearchString = 'SQL'
Go
 
 
 
 
 
/*4. Searching a specific data("SQL") in specific table("Table1") in specific columns("Column1") in current database*/
Exec Usp_FindString @TableName='Table1', @Columnname ='Column1', @SearchString = 'SQL'
Go
 

No comments:

Post a Comment