Categorysql

SQL Search all tables for text

I was looking for a string inside of a table in a massive database.  I was attempting to do this by trial-and-error until I ran across this method which does the searching for me.  This script searches all the text/string type fields in every table in the selected database.  Simply specify the “StringToFind”.

Declare @StringToFind VARCHAR(200), @Schema sysname, @Table sysname, @FullTable int, @NewMinID int, @NewMaxID int,
@SQLCommand VARCHAR(8000), @BaseSQLCommand varchar(8000), @Where VARCHAR(8000), @CountCheck varchar(8000) , @FieldTypes varchar(8000),
@cursor VARCHAR(8000), @columnName sysname, @SCn int, @SCm int
Declare @TableList table (Id int identity(1,1) not null, tablename varchar(250))
Declare @SQLCmds table (id int identity(1,1) not null, sqlcmd varchar(8000))
Declare @DataFoundInTables table (id int identity(1,1) not null, sqlcmd varchar(8000))

— Settings

SET @StringToFind = ‘.NET Framework 4.7.2’
SET NOCOUNT ON
SET @StringToFind = ‘%’+@StringToFind+’%’

— Gathering Info

if ((select count(*) from sysobjects where name = ‘tempcount’) > 0)
drop table tempcount

create table tempcount (rowsfound int)
insert into tempcount select 0

— This section here is to accomodate the user defined datatypes, if they have
— a SQL Collation then they are assumed to have text in them.
SET @FieldTypes = ”
select @FieldTypes = @FieldTypes + ”” + rtrim(ltrim(name))+”’,’ from systypes where collation is not null or xtype = 36
select @FieldTypes = left(@FieldTypes,(len(@FieldTypes)-1))

insert into @TableList (tablename)
select name from sysobjects
where xtype = ‘U’ and name not like ‘dtproperties’
order by name

— Start Processing Table List

select @NewMinID = min(id), @NewMaxID = max(id) from @TableList

while(@NewMinID <= @NewMaxID)
Begin

SELECT @Table = tablename, @Schema=’dbo’, @Where = ” from @TableList where id = @NewMinID

SET @SQLCommand = ‘SELECT * FROM ‘ + @Table + ‘ WHERE’
— removed ‘ + @Schema + ‘.

SET @cursor = ‘DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM [‘ + DB_NAME() + ‘].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ”’ + @Schema + ”’
AND TABLE_NAME = ”’ + @Table + ”’
AND DATA_TYPE IN (‘+@FieldTypes+’)’
–Original Check, however the above implements user defined data types –AND DATA_TYPE IN (”char”,”nchar”,”ntext”,”nvarchar”,”text”,”varchar”)’

EXEC (@cursor)

SET @FullTable = 0
DELETE FROM @SQLCmds

OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Where = @Where + ‘ [‘ + @columnName + ‘] LIKE ”’ + @StringToFind + ””
SET @Where = @Where + ‘ OR’

–PRINT @Table + ‘|’+ cast(len(isnull(@Where,”))+len(isnull(@SQLCommand,”)) as varchar(10))+’|’+@Where

if (len(isnull(@Where,”))+len(isnull(@SQLCommand,”)) > 3600)
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd) select @Where
SET @Where = ”
End

FETCH NEXT FROM col_cursor INTO @columnName
END

CLOSE col_cursor
DEALLOCATE col_cursor

if (@Where <> ”)
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd)
select @Where –select @Table,count(*) from @SQLCmds
End

SET @BaseSQLCommand = @SQLCommand

select @SCn = min(id), @SCm = max(id) from @SQLCmds
while(@SCn <= @SCm)
Begin

select @Where = sqlcmd from @SQLCmds where ID = @SCn

if (@Where <> ”)
Begin

SET @SQLCommand = @BaseSQLCommand + @Where
SELECT @CountCheck = ‘update tempcount set rowsfound = (select count(*) ‘+ substring(@SQLCommand,10,len(@SQLCommand)) + ‘)’
EXEC (@CountCheck)

if ((select rowsfound from tempcount) > 0)
Begin
PRINT ‘— [‘+cast(@NewMinID as varchar(15))+’/’+cast(@NewMaxID as varchar(15))+’] ‘+@Table + ‘ ———————————-[FOUND!]’
–PRINT ‘— [FOUND USING:] ‘ +@SQLCommand
insert into @DataFoundInTables (sqlcmd) select @SQLCommand
EXEC (@SQLCommand)
update tempcount set rowsfound = 0
End
else
Begin
PRINT ‘— [‘+cast(@NewMinID as varchar(15))+’/’+cast(@NewMaxID as varchar(15))+’] ‘+@Table
End
End

SET @SCn = @SCn + 1
End

set @NewMinID = @NewMinID + 1
end

if ((select count(*) from sysobjects where name = ‘tempcount’) > 0)
drop table tempcount

/*

This will now return all the sql commands you need to use

*/

select @NewMinID = min(id), @NewMaxID = max(id) from @DataFoundInTables

if (@NewMaxID > 0)
Begin
PRINT ‘ ‘
PRINT ‘ ‘
PRINT ‘—————————————–‘
PRINT ‘———– TABLES WITH DATA ————‘
PRINT ‘—————————————–‘
PRINT ‘ ‘
PRINT ‘We found ‘ + cast(@NewMaxID as varchar(10)) + ‘ table(s) with the string ‘+@StringToFind
PRINT ‘ ‘

while(@NewMinID <= @NewMaxID)
Begin

select @SQLCommand = sqlcmd from @DataFoundInTables where ID = @NewMinID

PRINT @SQLCommand

SET @NewMinID = @NewMinID + 1
End

PRINT ‘ ‘
PRINT ‘—————————————–‘

End

SQL Delete duplicate rows based on column

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

WITH CTE AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY computername ORDER BY ( SELECT 0)) RN
FROM temp_devicerefresh)
DELETE FROM CTE WHERE RN > 1

Replace string in a field

The example replaces the string height=”480″ with the string height=”320″.

UPDATE Blog SET myPost = REPLACE(CAST(myPost AS varchar(MAX)),’height=”480″‘, ‘height=”320″‘)
FROM Blog
WHERE CHARINDEX(‘height=”480″‘,CAST(myPost as varchar(MAX)))>0
AND myKey=2469

VB.NET Module for SQL Database

Imports System.Data.SqlClient

Module modDatabase

Public conn As New SqlClient.SqlConnection
Public myReader As SqlDataReader

Public dsRecords As New DataSet
Public daRecords As New SqlDataAdapter
Public MaxRecords As Integer

Public Sub OpenDatabase()
CloseSQLReader()
CloseDatabase()

conn.ConnectionString = “Server=.\sqlexpress;Database=DATABASE;Trusted_Connection=True;”
conn.Open()
End Sub

 

Public Sub CloseDatabase()
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
Application.DoEvents()
End Try
End Sub

 

Public Sub launchBrowser(ByVal browser As String, ByVal url As String)
Try
Process.Start(browser, url)
Catch ex As Exception
Application.DoEvents()
‘MessageBox.Show(ex.Message)
End Try
End Sub

 

Public Sub CloseSQLReader()
Try
myReader.Close()
Catch ex As Exception
Application.DoEvents()
End Try
End Sub

 

Public Sub ReadSqlData(ByVal sqlSelect As String)
Dim myCommand As New SqlCommand(sqlSelect, conn)
myReader = myCommand.ExecuteReader()
End Sub

 

Public Function ExecuteSqlCommand(ByVal sqlSelect As String) As Boolean
Dim flg As Boolean = False
Dim myCommand As New SqlCommand(sqlSelect, conn)

Try
flg = myCommand.ExecuteNonQuery
Catch
flg = False
End Try

Return flg
End Function

 

Public Sub HoldYourPants(ByVal Seconds As Integer)
System.Threading.Thread.Sleep(Seconds * 1000)
Application.DoEvents()
End Sub

End Module

SQL Left Function

The LEFT function allows you to extract a substring from a string, starting from the left-most character.

LEFT( string, number_of_characters )

Example:

SELECT LEFT('TechOnTheNet.com', 4);
Result: 'Tech'