SQL - Query Triggers in a Database

SELECT 
     o.name AS trigger_name 
    ,'x' AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(o.parent_obj) AS table_name 
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS o 

INNER JOIN sysobjects AS o2 
    ON o.parent_obj = o2.id 

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

*Credit goes to the poster here: Stack Overflow - Get Triggers in Database

SQL - Find Stored Procedures that have been Renamed

The following script will determine if any stored procedures were renamed, rather than dropped and re-created (substitute [DATABASE NAME] with the name of your database):

     SELECT meta.ROUTINE_NAME, *

     FROM [DATABASE NAME].sys.sql_modules def,

           [DATABASE NAME].INFORMATION_SCHEMA.ROUTINES meta

     WHERE  SUBSTRING(def.definition,1,4000) = SUBSTRING(meta.ROUTINE_DEFINITION,1,4000)

AND def.definition NOT LIKE '%![' + meta.ROUTINE_NAME + '!]%' ESCAPE '!'   

order by 1

SQL - Update Statement from Join

UPDATE    p

SET       p.phone_ext = d.phone

FROM      people p

JOIN      directory d

ON        p.last_name = d.last_name

SQL - Declare a Table Variable

DECLARE @table_membership TABLE(   seq_id int identity(1,1),

                                            name varchar(1000) NOT NULL,

                                            contact varchar(1000) NOT NULL,

                                            type_sort int NOT NULL,

                                   )

SQL - Update Table From Itself

UPDATE a
SET a.Price=(SELECT MAX(b.PRICE) FROM ITEM AS b WHERE b.Item=a.Item and b.Price is not null )
FROM  Item AS a 

*credit goes to this post: http://stackoverflow.com/questions/12151107/tsql-update-table-from-itself

SQL - Return Left of String from Specified Char

DECLARE @somestring as nvarchar(50) = 'test,value';

select left(@somestring,CHARINDEX(',',@somestring)-1)

SQL - Return Right of String from Specified Char

DECLARE @somestring as nvarchar(50) = 'test,value';

select right(@somestring,LEN(@somestring)-CHARINDEX(',',@somestring))

SQL - Check if Char Exists

DECLARE @somestring as nvarchar(50) = 'test,value';

select

[test] = CASE

  WHEN CHARINDEX(',', @somestring) > 0

    THEN 'exists'

    ELSE 'does not exist'

  END

SQL - Get Left Part of String From 2nd Space

If you have some text value and you need to get the left part of the string from the 2nd space, here is a technique I figured out that works.

declare @some_text as nvarchar(20) = 'through 123 some text';

select ltrim(rtrim(left(@some_text, charindex(' ', @some_text, charindex(' ', @some_text)+1))))

You could apply the same logic to go from the nth space in the occurrence, it would become more complex.

SQL - Remove Duplicates from a Delimited String

The following is a function to remove duplicates from a delimited string.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mga_distinct_list]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END

SQL - Format List with Commas & And

USE [######]

GO

/****** Object:  UserDefinedFunction [dbo].[format_list]    Script Date: 05/17/2012 09:39:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================

-- Author:        jefz

-- Create date: 5/17/2012

-- Description:   Use to turn a list that looks like this:

--                     

--                            Bob| Joe| Sally| Billy

--                     

--                      Into a list that looks like this:

--

--                            Bob, Joe, Sally and Billy

--

--                      A '|' is used to delimit the original list incase any of the names (or any

--                      string value used) includes a comma.  When the original list is created, it

--                      should be created with a '|'

-- ===========================================================================================

CREATE FUNCTION [dbo].[format_list]

(

      @list as nvarchar(max)

)

RETURNS nvarchar(max)

AS

BEGIN

      IF len(@list)-len(replace(@list, '|', '')) > 0

                 

                        BEGIN

                              select @list = reverse(@list)

                              select @list = left(@list, CHARINDEX ('|' ,@list)-1) + 'dna ' + replace(right(@list, len(@list)-CHARINDEX ('|' ,@list)), '|', ' ,')

                              select @list = reverse(@list)

                        END

                 

                  ELSE

                 

                        BEGIN

                              select @list = @list

                        END

     

                 

          RETURN @list

END

SQL - Return Multiple Records on One Line

If you have multiple records you want to appear on one line delimited by some character such as a comma, see below.

This would make:

Jones
Smith
Williams

Look like this:

Jones, Smith, Williams

DECLARE @all_names varchar(2000);

SELECT @all_names = COALESCE(@all_names + ', ', '') +

a.last_name 

FROM customers_table a

--WHERE .... (this is optional)

SELECT @all_names as 'last_names'

SQL - Zero Pad a Number

declare @somenumber int = 46;

select RIGHT(REPLICATE('0',3) + CAST(@somenumber AS NVARCHAR(3)),3)

This will take the number '46' and format it as a nvarchar to look like '046'. The number '3' in the expression specifies the number of digits you want the number to have.

SQL - Strip / Remove Lead Zeroes of a String

Cast the string to an int, then back into a nvarchar:


select CAST(CAST(substring('000422', 3, 4) as int) as nvarchar)

SQL - Capitalize the First Character of a String

UPPER(LEFT(some_string,1)) + RIGHT( some_string ,(LEN( some_string )-1))

SQL - Count Character Occurrence in a String

Want to count the number of times a specific character appears within a string? The line below will count the number of times the letter 'a' appears within the string value returned from the column 'description'.  I display the column for description and a column for the count side by side.

select description, len(description)-len(replace(description,'a','')) as 'count' from table_items