June 14, 2011 by Tim Karki

MSSQL Script to find keywords in Stored Procedure

SELECT o.type_desc AS ROUTINE_TYPE
        ,o.[name] AS ROUTINE_NAME
        ,m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE m.definition LIKE '%keyword%

June 1, 2011 by Tim Karki

Show all triggers in a database in MSSQL

Computer Stuff, Microsoft
LinkedIn, Microsoft, MSSQL, server, sql, triggers
Leave a comment

SELECT object_name(id), object_name(deltrig),
object_name(instrig), object_name(updtrig)
FROM sysobjects
WHERE xtype ='U'
AND
(deltrig > 0 OR instrig > 0 OR updtrig > 0)

October 9, 2010 by Tim Karki

Display SQL result horizontally (comma separated) in MSSQL 2005

Here is a sql statement to display a column result horizontally. e.g. record1, record2, record3, record4, ... recordx

DECLARE @id_list VARCHAR(MAX) --varchar max works for SQL 2005 or above
 
SELECT @id_list = CASE WHEN @id_list IS NULL THEN CONVERT(VARCHAR,column1) 
		ELSE @id_list + ',' + CONVERT(VARCHAR,column1) END
		FROM table1		
SELECT @id_list