Search

Show all current running Stored Procedures

Posted on Updated on

There are times when you need to figure out what Stored Procedures are currently running and if they are blocking any if all other processes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.start_time,
req.blocking_session_id,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
req.row_count
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = ‘MyDatabaseName

Advertisements

Find Columns in Table

Posted on Updated on

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EncTotChgAmt%’
ORDER BY schema_name, table_name;

Find a String in all Tables

Posted on Updated on

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET NOCOUNT ON;

DECLARE    @SearchStr NVARCHAR(100)

DROP TABLE #Results

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110)

SET  @TableName = ”

SET @SearchStr2 = QUOTENAME(‘%’ + ‘Muneeb’ + ‘%’,””)

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’, ‘int’, ‘decimal’)

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

Find a String in a Stored Procedure

Posted on Updated on

If you every decided to do something ridiculous and whatever you did requires that  all SP needs to be updated, here is how you can thumb through the SP in search of the string.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%AS400%’
AND ROUTINE_TYPE=‘PROCEDURE’