SQL : Data Search Script - Version 3

version 3 :

1) copes with schemas other than dbo!
2) restrict to tables only (was doing views!)
3) nolock table hint added
4) uniqueidentifier added to data types searched

/*
version 3 :    1) copes with schemas other than dbo!
        2) restrict to tables only (was doing views!)
        3) nolock table hint added
        4) uniqueidentifier added to data types searched

*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'searchstring'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
 and table_name not like 'sync%'
 and column_name <> 'order'
 and table_name in (select name from sys.sysobjects where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' +
 table_schema + '].[' + table_name +
 '] with (nolock) where [' +
 column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
 ' begin ' + char(10) +
 '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
 '  print ''' + table_schema + '.' + table_name  + '/' +column_name  + '''' + char(10) +
 ' end ' + char(10) +
 '' + char(10) +
 ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
 '''' + char(10)
from  information_schema.columns with (nolock)
where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
and table_name not like 'sync%'
and column_name <> 'order'
and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) + 'go'

Determine ASCII values of characters in a string

Determine ASCII values of characters in a string
-- script to determine ascii values of characters in data
-- adjust it to point at a table, not the test string!

set rowcount 1
declare @Cnt int
declare @charcount int
declare @title varchar(500)

select @Cnt = 1

declare @Characters table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL ,
letter char(1),
val int
)


select @title = 'the quick brown fox, jumped over the lazy dog'
-- select @title = column from table where criteriacolumn = criteriavalue

select @charcount = len(@title)
print @title

while @Cnt <= @charcount
begin
insert into @Characters (letter,val)
select substring(@title,@cnt,1), ascii(substring(@title,@cnt,1))
print substring(@title,@cnt,1)
print ascii(substring(@title,@cnt,1))
Select @Cnt = @Cnt + 1
end
set rowcount 0

SQL : Data Search Script - Version 2

sql data search : version 2
searches an entire database for a search string

1) displays progress of search in the messages window,
2) takes search term as a parameter
3) provides sql at the end to examine the located data

/*
version 2 :    1) displays progress of search in the messages window,
               2) takes search term as a parameter
               3) provides sql at the end to examine the located data
*/
declare @columncount int
declare @searchdata varchar(255)
-- set string to search for here
set @searchdata = 'Utility%Connections'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar')
 and table_schema = 'dbo'
 and table_name not like 'sync%'
 and column_name <> 'order' 
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_name + 
  '] where [' + 
  column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_name + '] where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
  '  print ''' + table_name  + '/' +column_name  + '''' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
  '''' + char(10)
 from  information_schema.columns
 where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar')
 and table_schema = 'dbo'
 and table_name not like 'sync%'
 and column_name <> 'order' 
union
select 'print @resultslist' + char(10) + 'go'

SQL Code Searching - Views

-- view search

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE VIEW_DEFINITION LIKE '%q=%' 

SQL Code Searching - Stored Procedures

-- sp search

select routine_name
    from information_schema.routines
    where routine_definition like '%code%'
    and routine_type = 'procedure'

SQL Code Searching - Views

-- view search

select table_name
    from information_schema.views
    where view_definition like '%code%'

SQL Schema Searching - Columns

-- column search

select table_name
from information_schema.columns
where column_name = 'columnname'