Monday, November 27, 2017

Find a specific text in a SQL Server Stored Procedure, Function, View

Declare @SearchString  varchar(50) = 'kotak_CMS..sf_subscriber'

Get Sp name with type and defination from sys.sql_modules and sys.objects tables
SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type, m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%'+@SearchString+'%'

Get Sp name from syscomment and sysobjects table
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%'+@SearchString+'%'

Get Sp name from sys.procedures table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@SearchString+'%'
GO

Find Column name in database tables
SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%Branch%'
ORDER BY    TableName ,ColumnName;
GO

Find Text in All Store Procedure
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%(7)%' 
    AND ROUTINE_TYPE='PROCEDURE'
GO

Wednesday, November 1, 2017

Below Query for fetch most trafic consuming query list. Most expensive, time consuming queries

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
    (CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
Where qt.dbid = DB_ID()
ORDER BY average_seconds DESC;