Beberapa T-SQL yang mungkin berguna untuk administrasi data dengan menggunakan SQL Server 2000 atau 2005.

1. sp_FindSPInSP
Store procedure dibawah ini digunakan untuk mencari suatu Store Procedure didalam Store Procedure. Artinya, jika didalam database anda, anda ingin mencari suatu store procedure "A" misalkan dipanggil di store procedure mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk mencari suatu Store Procedure didalam Store Procedure
--- EXEC sp_FindSPInSP 'MySP'
CREATE procedure [dbo].[sp_FindSPInSP]
(
@SPname varchar(3000)
)
AS
BEGIN

declare @id varchar(8000)

select @id=a.id
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='p'
and a.[name]=@SPname

--print @id

select b.depid, a.[name]
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='p' and b.depid=@id

END

2. sp_FindTableInSP
Store procedure dibawah ini digunakan untuk mencari suatu tabel tertentu didalam Store Procedure. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan dipanggil di store procedure mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk Mencari Tabel tertentu ada di Store Procedure mana aja
--- EXEC sp_FindTableInSP 'MyTabel'
CREATE procedure [dbo].[sp_FindTableInSP]
(
@tablename varchar(3000)
)
AS
BEGIN
select b.depid, a.[name]
into #allsp
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='p'

select a.name, a.id
into #alltable
from sysobjects a
where a.xtype='u' and a.name<>'dtproperties'

select tablename=t.name, spname=s.name
from #alltable t left join #allsp s
on t.id=s.depid
where t.name like '%'+@tablename+'%'
group by t.name, s.name
order by s.name asc

drop table #alltable
drop table #allsp

END

3. sp_FindFieldInSP
Store procedure dibawah ini digunakan untuk mencari suatu field tertentu didalam Store Procedure. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan dipanggil di store procedure mana aja, maka gunakanlah sp dibawah ini.


--- Digunakan untuk Mencari suatu field tertentu ada di Store Procedure mana aja
--- EXEC sp_FindFieldInSP 'YourFieldName'
CREATE proc [dbo].[sp_FindFieldInSP]
@FieldName varchar(200)
AS

declare @idsp int

CREATE TABLE #Hasil
(
NamaSP varchar(500)
)

DECLARE CurA CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR

select distinct b.id from SysColumns a inner join sysdepends b
on a.id=b.depid
where a.name COLLATE SQL_Latin1_General_CP1_CS_AS=@FieldName

OPEN CurA

FETCH NEXT FROM CurA INTO @idsp

WHILE @@FETCH_STATUS=0
BEGIN

--print @idsp
INSERT INTO #Hasil
(NamaSP)
select [name] from SysObjects
where id=@idsp and xtype='P'
order by [name]

FETCH NEXT FROM CurA INTO @idsp

END

CLOSE CurA
DEALLOCATE CurA

select * from #Hasil

DROP TABLE #Hasil

4. sp_FindFieldInTable
Store procedure dibawah ini digunakan untuk mencari suatu field tertentu didalam Tabel. Artinya, jika didalam database anda, anda ingin mencari suatu field "A" misalkan digunakan ditabel mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk Mencari Field tertentu ada di Tabel mana aja
--- EXEC sp_FindFieldInTable 'MyColumn'
CREATE PROC [dbo].[sp_FindFieldInTable]
@ColName varchar(200)
as
begin
Select SO.Name as TableName, SC.name as ColumnName From
SysColumns SC Inner Join SysObjects SO on SO.ID=SC.ID
Where lower(SC.name) like @ColName
Order By TableName
end

5. sp_FindTableInFunction
Store procedure dibawah ini digunakan untuk mencari suatu tabel tertentu didalam Function. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan digunakan difunction mana aja, maka gunakanlah sp dibawah ini.

--- DIGUNAKAN Untuk Mencari Tabel tertentu ada di Function mana aja
--- EXEC sp_FindTableInFunction 'MyTable'
CREATE procedure [dbo].[sp_FindTableInFunction]
(
@Tablename varchar(3000)
)
AS
BEGIN
select b.depid, a.[name]
into #allfunction
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype in ('FN','IF')

select a.name, a.id
into #alltable
from sysobjects a
where a.xtype='u' and a.name<>'dtproperties'

select tablename=t.name, functionname=fn.name
from #alltable t left join #allfunction fn
on t.id=fn.depid
where t.name like @Tablename
group by t.name, fn.name
order by fn.name asc

drop table #alltable
drop table #allfunction

END

6. sp_FindTableInView
Store procedure dibawah ini digunakan untuk mencari suatu tabel tertentu didalam View. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan digunakan di View mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk Mencari Tabel tertentu ada di View mana aja
--- EXEC sp_FindTableInView 'MyView'

CREATE procedure [dbo].[sp_FindTableInView]
(
@Tablename varchar(3000)
)
AS
BEGIN
select b.depid, a.[name]
into #allview
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='V'

select a.name, a.id
into #alltable
from sysobjects a
where a.xtype='u' and a.name<>'dtproperties'

select tablename=t.name, viewname=v.name
from #alltable t left join #allview v
on t.id=v.depid
where t.name like @Tablename
group by t.name, v.name
order by v.name asc

drop table #alltable
drop table #allview

END

7. sp_FindWhatInTable
Store procedure dibawah ini digunakan untuk mencari suatu nilai ( Value ) tertentu didalam seluruh tabel yang ada didalam database anda. Artinya, jika didalam database anda, anda ingin mencari suatu field "A001" misalkan digunakan di mana aja didalam seluruh database anda, maka gunakanlah sp dibawah ini.

Note : Karena ini akan mencari keseluruhan tabel didalam database anda, maka tergantung skenario relationship dan performance database anda, karena akan mengakibatkan eksekusi program akan lama.

--- Digunakan untuk mencari Nilai didalam Field ( Value) didalam Seluruh Tabel
--- EXEC sp_FindWhatInTable '31000000'
ALTER proc sp_FindWhatInTable
@ValueData varchar(100)
as
begin
declare @tablename varchar(50)
declare @columname varchar(50)
declare @tablename2 varchar(50)
declare @columname2 varchar(50)
--declare @ValueData varchar(100)
declare @SqlSelect varchar(5000)

declare @ColumnNameX varchar(200)
DECLARE @SqlSelect2 VARCHAR(2000)
DECLARE @Varkosong VARCHAR(8)
set @Varkosong='KOSONG'
--set @ValueData='310WO200704005900SRV'

create table #A
(

ColumnName varchar(500),
TableName varchar(500)
)

select a.name, a.id
into #column
from SysColumns a inner join sysdepends b
on a.id=b.depid
inner JOIN systypes c on c.xusertype = a.xtype

select name, id
into #table
from sysobjects
where xtype='u' and name<>'dtproperties'

select distinct
tablename=a.name,
columnname=b.name
into #all
from #table a inner join #column b
on a.id=b.id
ORDER BY b.name

DECLARE CurA CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR

select tablename,columnname from #all

OPEN CurA

FETCH NEXT FROM CurA INTO @tablename, @columname

WHILE @@FETCH_STATUS=0
BEGIN

SET @SqlSelect='IF EXISTS (SELECT ' + @columname + ' FROM ' + @tablename +
' WHERE ' + @columname + ' LIKE ''%' + @ValueData + '%'''

SET @SqlSelect=@SqlSelect + ') INSERT INTO #A' + ' SELECT ''' + @columname + ''' , ''' + @tablename + ''' FROM ' + @tablename +
' WHERE ' + @columname + ' LIKE ''%' + @ValueData + '%'''

SET @SqlSelect=@SqlSelect + ' ELSE ' + 'INSERT INTO #A SELECT ''' + @VarKosong + '''' + ' , ' + '''' + @VarKosong + ''''
PRINT @SqlSelect

exec (@SqlSelect)

FETCH NEXT FROM CurA INTO @tablename, @columname

END

CLOSE CurA
DEALLOCATE CurA

--select * from #A

select DISTINCT TableName, ColumnName
INTO #B
from #A
WHERE TableName<>'KOSONG'

select * from #B

drop table #table
drop table #column
drop table #A
drop table #all
drop table #B

end

Demikian sedikit tutorial t-sql ini disharring, Semoga berguna untuk teman-teman khususnya yang berkecimpung didalam t-sql programming.

Regards,