Sql Yararli Kod Parçaları

Search.sql
Veritabanı objeleri (stored procedure, trigger, function … ) içerisinde metin arama için kullanabileceğimiz bir search saklı yordamını aşağıda görebilirsiniz.

-- Veritabanı nesne içerikleri
SELECT * FROM sys.syscomments(NOLOCK)

-- Şema isimlerinin tutulduğu tablo
SELECT * FROM sys.schemas(NOLOCK)

-- Bütün veritabanı nesne isimleri
SELECT * FROM sys.all_objects(NOLOCK)

-- ana select ifadesi
SELECT  S.name AS SP_SCHEMA,
        O.name AS SP_NAME,
        C.text AS SP_TEXT
FROM sys.syscomments(NOLOCK) AS C
    JOIN sys.all_objects(NOLOCK) AS O
        ON C.id = O.object_id
    JOIN sys.schemas AS S
        ON S.schema_id = O.schema_id
WHERE O.type in ('P','FN','IF','FS','AF','X','TF','TR','PC') AND
      C.text like '%' + 'ARANACAK KELIME' + '%'

 Nested_Search.sql

İç içe arama yapacağınız bir sql yordamı
DECLARE @text1 VARCHAR(MAX),
        @text2 VARCHAR(MAX),
        @text3 VARCHAR(MAX),
        @text4 VARCHAR(MAX),
        @text5 VARCHAR(MAX),
        @dbname VARCHAR(64)

SET @dbname='DB_NAME'
SET @text1='TEXT1_TO_SEARCH'
SET @text2='TEXT2_TO_SEARCH'
SET @text3='TEXT3_TO_SEARCH'
SET @text4='TEXT4_TO_SEARCH'
SET @text5='TEXT5_TO_SEARCH'

DECLARE @sql VARCHAR(MAX)
SELECT @sql = ''

SELECT @sql = @sql + 'SELECT * FROM ('
SELECT @sql = @sql + 'SELECT * FROM ('
SELECT @sql = @sql + 'SELECT * FROM ('
SELECT @sql = @sql + 'SELECT * FROM ('

select @sql = @sql + 'SELECT ''' + @dbname + ''' AS db, o.name,m.definition '
select @sql = @sql + ' FROM '+@dbname+'.sys.sql_modules m '
select @sql = @sql + ' INNER JOIN '+@dbname+'..sysobjects o on m.object_id=o.id'
select @sql = @sql + ' WHERE [definition] LIKE ''%'+@text1+'%'''

SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text2+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text3+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text4+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text5+'%'''

--PRINT @sql
execute (@sql)


OpenTransactionListingAndKill.sql

Şirket veritabanlarında birden fazla kişi ve takım işlem yapar.Bu işlemlerden bazıları sonlanmayabilir ve bu sonlanamayan transactionlar veritabanı performansını doğrudan etkiler.Aşağıda sql server üzerinde sistem tablolarını kullanarak açık kalan (sonlanmayan) transactionları listeleyebilir ve bu transactionları (kill) sonlandırabilirsiniz.
-- Açık olan transactionları listeler

SELECT  L.request_session_id AS SPID,
  DB_NAME(L.resource_database_id) AS DatabaseName,
  O.Name AS LockedObjectName,
  P.object_id AS LockedObjectId,
  L.resource_type AS LockedResource,
  L.request_mode AS LockType,
  ST.text AS SqlStatementText,
  ES.login_name AS LoginName,
  ES.host_name AS HostName,
  TST.is_user_transaction as IsUserTransaction,
  AT.name as TransactionName,
  CN.auth_scheme as AuthenticationMethod
FROM     sys.dm_tran_locks L
 JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
 JOIN sys.objects O ON O.object_id = P.object_id
 JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
 JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
 JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
 JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
 CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

--SPID ile açık olan transactionları KILL ediyoruz

--KILL SPID



INSERT.sql

Bu saklı yordam ; bir tabloya ekledimiş olduğunuz veriler için insert işelmini yapan ifadeyi hazırlar.Şöyle düşünün test ortamında bir tabloya bir sürü veri eklediniz eklediğiniz bu verileri diğer ortamlara kolayca taşımak istiyorsunuz.bunun için tek tek insert scripti hazırlamanız gerekiyor.
GO
/****** Object:  StoredProcedure [dbo].[INSERT]    Script Date:  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--drop proc [dbo].[INSERT]

CREATE procedure  [dbo].[INSERT]
(
   @Query  Varchar(MAX)
)

AS

SET nocount ON

DECLARE @WithStrINdex AS INT
DECLARE @WHEREStrINdex AS INT
DECLARE @INDExtouse AS INT

DECLARE @SchemaANDTAble VArchar(270)
DECLARE @Schema_name  varchar(30)
DECLARE @Table_name  varchar(240)
DECLARE @Condition  Varchar(MAX)

SET @WithStrINdex=0

SELECT @WithStrINdex=CHARINDEX('With',@Query )
, @WHEREStrINdex=CHARINDEX('WHERE', @Query)

IF(@WithStrINdex!=0)
SELECT @INDExtouse=@WithStrINdex
ELSE
SELECT @INDExtouse=@WHEREStrINdex

SELECT @SchemaANDTAble=LEFT (@Query,@INDExtouse-1)
SELECT @SchemaANDTAble=LTRIM (RTRIM( @SchemaANDTAble))

SELECT @Schema_name= LEFT (@SchemaANDTAble, CharINdex('.',@SchemaANDTAble )-1)
,      @Table_name = SUBSTRING(  @SchemaANDTAble , CharINdex('.',@SchemaANDTAble )+1,LEN(@SchemaANDTAble) )

,      @Condition=SUBSTRING(@Query,@WHEREStrINdex+6,LEN(@Query))--27+6

DECLARE   @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )
DECLARE @CONDITIONS AS varchar(MAX)
DECLARE @Total_Rows AS SmallINT
DECLARE @Counter AS SmallINT

DECLARE @ComaCol AS varchar(MAX)
SELECT @ComaCol=''

SET @Counter=1
SET @CONDITIONS=''

INsert INTO @COLUMNS
SELECT  Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FROM INFORMATION_SCHEMA.columns WHERE Table_schema=@Schema_name
AND table_name=@Table_name
AND Column_Name NOT IN ('FTP_PASSWORD','FTP_ENCRYPTED_PASSWORD')

SELECT @Total_Rows= Count(1) FROM  @COLUMNS

             SELECT @Table_name= '['+@Table_name+']'

             SELECT @Schema_name='['+@Schema_name+']'

While (@Counter<=@Total_Rows )
begIN
--PRINT @Counter

    SELECT @ComaCol= @ComaCol+'['+Column_Name+'],'
    FROM @COLUMNS
WHERE [Row_number]=@Counter

SELECT @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+

 Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  )

  +'''''''' end+'+''','''

FROM @COLUMNS
WHERE [Row_number]=@Counter
AND Column_name NOT IN ('FTP_PASSWORD','FTP_ENCRYPTED_PASSWORD')

SET @Counter=@Counter+1

End

SELECT @CONDITIONS=RIGHT(@CONDITIONS,LEN(@CONDITIONS)-2)

SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)
SELECT @ComaCol= substrINg (@ComaCol,0,  len(@ComaCol) )

SELECT @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS

SELECT @CONDITIONS=@CONDITIONS+'+'+ ''')'''

--PrINt(@Condition)
SELECT @CONDITIONS= 'SELECT  '+@CONDITIONS +'FROM  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' WHERE '+@Condition
--prINt(@CONDITIONS)
Exec(@CONDITIONS)

/****** Kullanımı  ******/
EXEC [dbo].[INSERT] 'dbo.TABLE WHERE COLUMN_NAME=''TEXT'''

Detaylar