Search all columns in a database for text

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:


CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- 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
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 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'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

Fix Orphaned SQL Server Users

EXEC sp_change_users_login 'Auto_Fix', 'username'

Analyse table space usage

-- Script to analyse table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7 and 2000

set nocount on
create table #TABLE_SPACE_WORK
(
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)

create table #TABLE_SPACE_USED
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)

create table #TABLE_SPACE
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MB numeric(18,4) not null,
USED_GB numeric(18,4) not null,
AVERAGE_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null,
)

declare @fetch_status int

declare @proc varchar(200)
select @proc = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
TABLE_NAME =
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
order by
1

open Cur_Cursor

declare @TABLE_NAME varchar(200)

select @fetch_status = 0

while @fetch_status = 0
begin

fetch next from Cur_Cursor
into
@TABLE_NAME

select @fetch_status = @@fetch_status

if @fetch_status <> 0
begin
continue
end

truncate table #TABLE_SPACE_WORK

insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'


-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME

insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK

end --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW

)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED =
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA =
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE =
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED =
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE

Finding All Stored Procedures Containing Text

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%searchtext%'
AND ROUTINE_TYPE='PROCEDURE'

Fixed Table Header in a Scrollable GridView

Set the style of the header row as follows.  The GridView should be placed in a div with style overflow:auto.

table th
{
border-width: 4px;
border-color: Black;
background-color: Gray;
position: relative;
top: expression(this.parentNode.parentNode.
           parentNode.scrollTop-1);
}