CodeKeep SQL Feed

Change Maintenance Plan Owner

Wed, 11 Nov 2009 14:33:28 GMT

Description: Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005.

Link: http://www.codekeep.net/snippets/1698486a-29a8-4256-9af1-80bfba1dabfc.aspx

/*Here's how to change the owner of a maintenance plan in SQL Server 2005*/
--To find the name and owner of the maintenance plan.
--select * from msdb.dbo.sysdtspackages90
--To find the sid you want to use for the new owner.
--select * from sys.database_principals

UPDATE [msdb].[dbo].[sysdtspackages90]
SET [ownersid] = 0x01
WHERE [name] = 'MaintenancePlan'

Create Primary Key

Thu, 29 Oct 2009 19:57:26 GMT

Description: Create a primary key using TSQL

Link: http://www.codekeep.net/snippets/50968dff-3ffb-4082-93b7-e48cca90d4a4.aspx

ALTER TABLE tablename ADD PRIMARY KEY (PlayerID)

Rebuild Indexs on all tables for current database

Tue, 27 Oct 2009 19:34:11 GMT

Description: rebuilds index's for current database

Link: http://www.codekeep.net/snippets/af56a3cd-679d-45eb-ad92-2d45ebda726c.aspx

DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.['' + table_schema + ''].['' + table_name + '']'' as tableName   
                    FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

       -- SQL 2000 command  
       --DBCC DBREINDEX(@Table,' ',@fillfactor)   
       print 'Rebuilding indexs on table ' + @Table + ' ...'
       -- SQL 2005 command  
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  
       EXEC (@cmd)  

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor

TSQL:: if column exists

Mon, 26 Oct 2009 02:25:19 GMT

Description: TSQL query check if column exists

Link: http://www.codekeep.net/snippets/00f06f6f-2f2f-4e2a-ade4-3651fa5aadc4.aspx

IF NOT EXISTS (Select Coalesce(Col_length('protocoldocument','IncludeEditable'),0))

BEGIN
            ALTER TABLE protocoldocument 
            ADD IncludeEditable Bit Null
END

TSQL:: check if constraint exists

Mon, 26 Oct 2009 02:23:34 GMT

Description: TSQL query check if constraint exists

Link: http://www.codekeep.net/snippets/3dea9118-f3ee-46a8-b44c-7f919dceca48.aspx

IF EXISTS(

            select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

            where table_name = 'protocoldocument' 

            and constraint_name = 'UK_ProtocolDocument_NameTypeVersionProtocolId'

 )

BEGIN

            ALTER TABLE ProtocolDocument DROP CONSTRAINT UK_ProtocolDocument_NameTypeVersionProtocolId

END

TSQL:: Check foreign keys that reference current table

Mon, 26 Oct 2009 02:21:42 GMT

Description: TSQL query that will check foreign keys that reference current table

Link: http://www.codekeep.net/snippets/5d281c46-7a20-4b78-b16a-9201444e41a5.aspx

select

            object_name(fkeyid) as [Table],

            object_name(constid) as [Foreign Key Name],

            a.name [Column],

            object_name(rkeyid) as [References],

            b.name [Referenced Column]

from sysforeignkeys f

inner join syscolumns a on a.id = f.fkeyid and a.colid = f.fkey

inner join syscolumns b on b.id = f.rkeyid and b.colid = f.rkey

where 

            -- fkeyid = object_id( N'protocolBoardReview' )

            -- and a.name = N'Id'

             object_name(rkeyid) = 'protocolBoardReview'

             and b.name = N'Id'

TSQL split string function

Mon, 26 Oct 2009 02:18:15 GMT

Description: TSQL function used to spit a string by checking the given delimiter

Link: http://www.codekeep.net/snippets/78c2ee64-c4bc-4f05-9c09-614e1be3bcc6.aspx

CREATE FUNCTION [dbo].[fnSplit] (

            @str_in VARCHAR(8000),

            @separator VARCHAR(4)

)           RETURNS @strtable TABLE (strval VARCHAR(8000))

AS

            BEGIN

                        DECLARE @Occurrences INT, @Counter INT, @tmpStr VARCHAR(8000)

                        SET @Counter = 0

 

                        IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator 

                                    SET @str_in = @str_in + @separator

                                    

                        SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

                        SET @tmpStr = @str_in

 

                        WHILE @Counter <= @Occurrences 

                        BEGIN

                                    SET @Counter = @Counter + 1

                                    INSERT INTO @strtable VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

 

                                    SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

 

                                    IF DATALENGTH(@tmpStr) = 0

                                                BREAK

                        END

 

                        RETURN 

 

            END

Get hierarchies of childs by using SQL Server

Wed, 07 Oct 2009 09:42:21 GMT

Description: Get hierarchies of childs by using SQL Server

Link: http://www.codekeep.net/snippets/4ff56437-0702-442c-87df-bbe781b1d9ef.aspx

irst I create a table

    Create Table ParentChild_Table
    (ID int identity(100,1),[Name]varchar(100),ParentID int)

Then insert some records

    INSERT INTO ParentChild_Table VALUES('JOHN',0)
    INSERT INTO ParentChild_Table VALUES ('RINA',100)
    INSERT INTO ParentChild_Table VALUES ('RAJU',100)
    INSERT INTO ParentChild_Table VALUES ('RAJEEV',101)
    INSERT INTO ParentChild_Table VALUES ('RAVI',103)
    INSERT INTO ParentChild_Table VALUES ('ALBERT',102)
    INSERT INTO ParentChild_Table VALUES ('NITIN',104)
    INSERT INTO ParentChild_Table VALUES ('ALBERT',104)
    INSERT INTO ParentChild_Table VALUES ('A1',0)
    INSERT INTO ParentChild_Table VALUES ('A1.1',108)
    INSERT INTO ParentChild_Table VALUES ('A1.2',108)
    INSERT INTO ParentChild_Table VALUES ('A1.1.1',109)


Create Procedure GettingChildhierarchy
@ID int
as
Set Nocount on
-- table #Temp1 store all the PID  to be returned         
create table #Temp1(PID int)         
--table #Temp2 contains PID in the previous iteration         
create table #Temp2(PID int)         
--table #Temp3 is a temporary table used to store intermediate results         
create table #Temp3(PID int)

insert into #Temp2(PID )         
select ID from [ParentChild_Table]  where ParentID= @ID        
union          
select ID  from [ParentChild_Table]  where ID= @ID        

while exists(select * from #Temp2)         
begin         
insert into #Temp1(PID)         
select #Temp2.PID from #Temp2         

insert into #Temp3(PID)         
select ID  from [ParentChild_Table], #Temp2  where #Temp2.PID=[ParentChild_Table].[ParentID]         

delete from #Temp2         
insert into #Temp2         
select * from #Temp3         
delete from #Temp3          
end

---End       
-------Start sava distinct PIDId in another temp table       
select * from ParentChild_Table where ID in( select distinct* from #Temp1)
drop table #Temp1 
drop table #Temp2
drop table #Temp3  
-------end
Go   


"Exec GettingChildhierarchy 108"

Split sql datetime to date and time columns

Thu, 17 Sep 2009 05:44:03 GMT

Description: Select CONVERT(varchar, CreationDate, 101) Date, convert(varchar(10), CreationDate, 108) Time,

Link: http://www.codekeep.net/snippets/086d1412-2339-429a-a31b-bed792870466.aspx

Select CONVERT(varchar, CreationDate, 101) Date, convert(varchar(10), CreationDate, 108) Time,

Cut String from Viatadb column

Wed, 09 Sep 2009 02:49:20 GMT

Description: Cut String from Viatadb column

Link: http://www.codekeep.net/snippets/bb1deeb5-b9b2-4533-9f43-8c4b941e3153.aspx

UPDATE EmployeeT
SET DateEntery = SUBSTRING(DateEntery,1,10)

Get SQL Server Path from registry

Tue, 01 Sep 2009 03:42:57 GMT

Description: Reads the SQL server path from registry. From: http://www.builderau.com.au/program/sqlserver/soa/Obtain-your-SQL-Server-s-database-location/0,339028455,320283099,00.htm

Link: http://www.codekeep.net/snippets/a8156a93-0639-468c-82b6-f26baec6f678.aspx

DECLARE @retvalue int, @data_dir varchar(500)
EXECUTE @retvalue = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLDataRoot', @param = @data_dir OUTPUT
PRINT 'SQL Server Data Path: '+ @data_dir

Long-Running SQL Queries

Thu, 27 Aug 2009 07:22:08 GMT

Description: Discover Long-Running Queries http://www.devx.com/tips/Tip/42362

Link: http://www.codekeep.net/snippets/94761c9c-ac28-4419-9e47-92effbf85471.aspx

Let SQL Server tell you which queries take the longest to run. The following query returns the five longest-running queries. You can change the TOP 5 to whatever number best meets your needs.

SELECT TOP 5 t.TEXT query, 
   stats.max_elapsed_time 
AS MaxElapsedTime,
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC

Concatinating SQL stings with nulls

Thu, 13 Aug 2009 14:51:03 GMT

Description: 'test' + null + 'srt' returns null set concat_null_yields_null off; 'test' + null + 'srt' returns 'testsrt'

Link: http://www.codekeep.net/snippets/ed5aac46-3a03-4752-9c35-f5e57ce5c7bd.aspx

set concat_null_yields_null off;

Generated FK script

Fri, 17 Jul 2009 13:44:30 GMT

Description: This script generate the script for all Foreign Key on a Data base ps this is a improvement of a script made by Jake Massey

Link: http://www.codekeep.net/snippets/7f3466ad-2de8-49e7-bcf6-0051e08baa56.aspx

declare @User     varchar(128)
declare @TabName  varchar(128)
declare @RefName  varchar(128)
declare @FKName   varchar(128)
declare @STMT     varchar(MAX)


PRINT '/* ' 
PRINT ' * Scripting took palce on ' + Cast(GetDate() as varchar(30))
PRINT ' */'

declare FKCur cursor for
  select fk.name, t.name, u.name
  from sysobjects fk inner join sysobjects t
    on fk.parent_obj = t.id
    inner join sysusers u 
    on u.uid = t.uid
  where t.type = 'U'
  and fk.type = 'F'
  and t.name <> 'dtproperties'

open FKCur 

fetch next from FKCur into @FKName, @TabName, @User

	while @@FETCH_STATUS = 0
	  begin
		select @STMT = 

'if (select count(*) from dbo.sysobjects where id = object_id(N''' + '['+ @User + '].[' + @TabName + ']' +''')) = 1
'+
'begin 
' +
'    if (select count(*) from dbo.sysobjects where id = object_id(N''' + '['+ @User + '].[' + @FKName + ']' +''')) = 0
'+
'    begin 
' +
'print ''' + @FKName + '''
' +
'     ALTER TABLE [' + @User + '].[' + @TabName + '] ADD 
	   CONSTRAINT [' + @FKName + '] FOREIGN KEY
	 	( 
		'

		------------------------------------------------------
		declare @Col1  varchar(128)
		declare @Col2  varchar(128)
		declare @Col3  varchar(128)
		declare @Col4  varchar(128)
		declare @Col5  varchar(128)
		declare @Col6  varchar(128)
		declare @Col7  varchar(128)
		declare @Col8  varchar(128)
		declare @Col9  varchar(128)
		declare @Col10 varchar(128)
		declare @Col11 varchar(128)
		declare @Col12 varchar(128)
		declare @Col13 varchar(128)
		declare @Col14 varchar(128)
		declare @Col15 varchar(128)
		declare @Col16 varchar(128)

		declare @Ref1  varchar(128)
		declare @Ref2  varchar(128)
		declare @Ref3  varchar(128)
		declare @Ref4  varchar(128)
		declare @Ref5  varchar(128)
		declare @Ref6  varchar(128)
		declare @Ref7  varchar(128)
		declare @Ref8  varchar(128)
		declare @Ref9  varchar(128)
		declare @Ref10 varchar(128)
		declare @Ref11 varchar(128)
		declare @Ref12 varchar(128)
		declare @Ref13 varchar(128)
		declare @Ref14 varchar(128)
		declare @Ref15 varchar(128)
		declare @Ref16 varchar(128)

		declare @RefTable varchar(128)
		declare @RefOwner varchar(128)
		
		declare @Update varchar(128)
		declare @Delete varchar(128)
		
		
		declare ColCur cursor for
		  select col_name( object_id( sof.name ), sr.fkey1 ), 
			     col_name( object_id( sof.name ), sr.fkey2 ),
			     col_name( object_id( sof.name ), sr.fkey3 ),
			     col_name( object_id( sof.name ), sr.fkey4 ),
			     col_name( object_id( sof.name ), sr.fkey5 ),
			     col_name( object_id( sof.name ), sr.fkey6 ),
			     col_name( object_id( sof.name ), sr.fkey7 ),
			     col_name( object_id( sof.name ), sr.fkey8 ),
			     col_name( object_id( sof.name ), sr.fkey9 ),
			     col_name( object_id( sof.name ), sr.fkey10 ),
			     col_name( object_id( sof.name ), sr.fkey11 ),
			     col_name( object_id( sof.name ), sr.fkey12 ),
			     col_name( object_id( sof.name ), sr.fkey13 ),
			     col_name( object_id( sof.name ), sr.fkey14 ),
			     col_name( object_id( sof.name ), sr.fkey15 ),
			     col_name( object_id( sof.name ), sr.fkey16 ),
				 ----------------------------------------------
			     col_name( object_id( sor.name ), sr.rkey1 ), 
			     col_name( object_id( sor.name ), sr.rkey2 ),
			     col_name( object_id( sor.name ), sr.rkey3 ),
			     col_name( object_id( sor.name ), sr.rkey4 ),
			     col_name( object_id( sor.name ), sr.rkey5 ),
			     col_name( object_id( sor.name ), sr.rkey6 ),
			     col_name( object_id( sor.name ), sr.rkey7 ),
			     col_name( object_id( sor.name ), sr.rkey8 ),
			     col_name( object_id( sor.name ), sr.rkey9 ),
			     col_name( object_id( sor.name ), sr.rkey10 ),
			     col_name( object_id( sor.name ), sr.rkey11 ),
			     col_name( object_id( sor.name ), sr.rkey12 ),
			     col_name( object_id( sor.name ), sr.rkey13 ),
			     col_name( object_id( sor.name ), sr.rkey14 ),
			     col_name( object_id( sor.name ), sr.rkey15 ),
			     col_name( object_id( sor.name ), sr.rkey16 ),
				 sor.name, su.name,
				 CASE WHEN (objectproperty(constid, 'CnstIsUpdateCascade') = 1)
					   THEN 'ON UPDATE CASCADE' ELSE ' ' END,
				 CASE WHEN (objectproperty(constid, 'CnstIsDeleteCascade') = 1)
					   THEN 'ON DELETE CASCADE' ELSE ' ' END
		  from sysreferences sr inner join sysobjects sof on 
			sof.id = sr.fkeyid
		  inner join sysobjects sor on 
			sor.id = sr.rkeyid
		  inner join sysusers su on sor.uid = su.uid
		  where sr.constid = object_id(@FKName)
		
		open ColCur
  
        fetch next from ColCur into @Col1 , @Col2 , @Col3 , @Col4 , @Col5 , @Col6 , @Col7 ,
									@Col8 , @Col9 , @Col10, @Col11, @Col12, @Col13, @Col14,
									@Col15, @Col16,
									@Ref1 , @Ref2 , @Ref3 , @Ref4 , @Ref5 , @Ref6 , @Ref7 ,
									@Ref8 , @Ref9 , @Ref10, @Ref11, @Ref12, @Ref13, @Ref14,
									@Ref15, @Ref16,
									@RefTable, @RefOwner,
									@Update, @Delete
		if @Col1 = NULL 
			set @Col1 = ''
		if @Col2 = NULL
			set @Col2 = ''
		if @Col3 = NULL
			set @Col3 = ''
		if @Col4 = NULL
			set @Col4 = ''
		if @Col5 = NULL
			set @Col5 = ''
		if @Col6 = NULL
			set @Col6 = ''
		if @Col7 = NULL
			set @Col7 = ''
		if @Col8 = NULL
			set @Col8 = ''
		if @Col9 = NULL
			set @Col9 = ''
		if @Col10 = NULL
			set @Col10 = ''
		if @Col11 = NULL
			set @Col11 = ''
		if @Col12 = NULL
			set @Col12 = ''
		if @Col13 = NULL
			set @Col13 = ''
		if @Col14 = NULL
			set @Col14 = ''
		if @Col15 = NULL
			set @Col15 = ''
		if @Col16 = NULL
			set @Col16 = ''
------------------------------
		if @Ref1 = NULL 
			set @Ref1 = ''
		if @Ref2 = NULL
			set @Ref2 = ''
		if @Ref3 = NULL
			set @Ref3 = ''
		if @Ref4 = NULL
			set @Ref4 = ''
		if @Ref5 = NULL
			set @Ref5 = ''
		if @Ref6 = NULL
			set @Ref6 = ''
		if @Ref7 = NULL
			set @Ref7 = ''
		if @Ref8 = NULL
			set @Ref8 = ''
		if @Ref9 = NULL
			set @Ref9 = ''
		if @Ref10 = NULL
			set @Ref10 = ''
		if @Ref11 = NULL
			set @Ref11 = ''
		if @Ref12 = NULL
			set @Ref12 = ''
		if @Ref13 = NULL
			set @Ref13 = ''
		if @Ref14 = NULL
			set @Ref14 = ''
		if @Ref15 = NULL
			set @Ref15 = ''
		if @Ref16 = NULL
			set @Ref16 = ''
------------------------------
			if @Col1 <> '' 
 			 begin
			  set @STMT = @STMT + '  [' + @Col1 + ']' + Char(13)
			 end
			if @Col2 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col2 + ']' + Char(13)
			 end
			if @Col3 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col3 + ']' + Char(13)
			 end
			if @Col4 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col4 + ']' + Char(13)
			 end
			if @Col5 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col5 + ']' + Char(13)
			 end
			if @Col6 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col6 + ']' + Char(13)
			 end
			if @Col7 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col7 + ']' + Char(13)
			 end
			if @Col8 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col8 + ']' + Char(13)
			 end
			if @Col9 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col9 + ']' + Char(13)
			 end
			if @Col10 <> '' 
			 begin
			  set @STMT = @STMT + '		, [' + @Col10 + ']' + Char(13)
			 end
			if @Col11 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col11 + ']' + Char(13)
			 end
			if @Col12 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col12 + ']' + Char(13)
			 end
			if @Col13 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col13 + ']' + Char(13)
			 end
			if @Col14 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col14 + ']' + Char(13)
			 end
			if @Col15 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col15 + ']' + Char(13)
			 end
			if @Col16 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Col16 + ']' + Char(13)
			 end
---------------------------------------------------------------
			set @STMT = @STMT + 
'		 )REFERENCES [' + @RefOwner + '].[' + @RefTable + '] ( 
		'
---------------------------------------------------------------
			if @Ref1 <> '' 
 			 begin
			  set @STMT = @STMT + '  [' + @Ref1 + ']' + Char(13)
			 end
			if @Ref2 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref2 + ']' + Char(13)
			 end
			if @Ref3 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref3 + ']' + Char(13)
			 end
			if @Ref4 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref4 + ']' + Char(13)
			 end
			if @Ref5 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref5 + ']' + Char(13)
			 end
			if @Ref6 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref6 + ']' + Char(13)
			 end
			if @Ref7 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref7 + ']' + Char(13)
			 end
			if @Ref8 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref8 + ']' + Char(13)
			 end
			if @Ref9 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref9 + ']' + Char(13)
			 end
			if @Ref10 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref10 + ']' + Char(13)
			 end
			if @Ref11 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref11 + ']' + Char(13)
			 end
			if @Ref12 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref12 + ']' + Char(13)
			 end
			if @Ref13 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref13 + ']' + Char(13)
			 end
			if @Ref14 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref14 + ']' + Char(13)
			 end
			if @Ref15 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref15 + ']' + Char(13)
			 end
			if @Ref16 <> ''
			 begin
			  set @STMT = @STMT + '		, [' + @Ref16 + ']' + Char(13)
			 end
------------------------------------------------------------------
			set @STMT = @STMT + '        ) ' + @Update + ' ' + @Delete + '
    end
end
GO' + Char(13) + Char(13)


			PRINT @STMT

		Close ColCur
		Deallocate ColCur

	   fetch next from FKCur into @FKName, @TabName, @User
	  end 

close FKCur
deallocate FKCur

Find a string in Procedures, Triggers, Constraints, Defaults, Functions, and Views

Fri, 17 Jul 2009 02:30:35 GMT

Description: Find a string in Procedures, Triggers, Constraints, Defaults, Functions, and Views

Link: http://www.codekeep.net/snippets/3a382fa2-ab69-44d5-9cf4-a6d677f0b10a.aspx

--**************************************
--     
-- Name: Find a string in Procedures, Tr
--     iggers, Constraints, Defaults, Functions
--     ,and Views
-- Description:Allow users to search thr
--     u objects for a value they are looking. 
--     This may be items that touch a specific 
--     table/view or items that need to be repl
--     aced.
-- By: James Travis
--
-- Inputs:@find = the value(s) to search
--      for
@type = the type OF objects TO search
--
-- Returns:The names of objects that con
--     taint th search criteria.
--
-- Assumes:First off this will not work 
--     for any items that have the WITH ENCRYPT
--     ION remark in them. With this is can pos
--     e a string such as 'INSERT' against all 
--     the 'P'rocedures to get a return of whic
--     h Procedures have an INSERT statment in 
--     them or you can do word strings such as 
--     'FROM TABLE1 WHERE COL1 =' or string lis
--     t searches such as 'INSERT%TABLE1%COL1 =
--     '. You can use any valid like strings yo
--     u wish, but you don't need leading and e
--     nding wildcards as general most items st
--     art with a specific item such as CREATE 
--     which will be contained in the majority 
--     of code items. This is compatible with S
--     QL 7/2000.
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=414&lngWId=5--for details.--**************************************
--     

CREATE PROCEDURE sp_FindStringInCode
/* Input variables, DEFAULT NULL FOR custom error output. */
@find VARCHAR(50) = NULL,
@type VARCHAR(2) = NULL
AS
/* CHECK FOR NULL or invalid input AND show custom error. */
IF @find IS NULL AND @type IS NULL


    BEGIN
    	RAISERROR ('This PROCEDURE has two required parameters @find AND @type',16,-1)
    	RETURN
END
ELSE IF @find IS NULL


    BEGIN
    	RAISERROR ('You must enter a valid LIKE criteria FOR @find without the leading/ending % wildcard.',16,-1)
    	RETURN
END
ELSE IF @type IS NULL OR @type NOT IN ('C','D','FN','P','TR','V')


    BEGIN
    	RAISERROR('No value was entered FOR @type.
    Valid VALUES FOR @type are
    	C = CHECK CONSTRAINT
    	D = DEFAULT
    	FN = Function
    	P = PROCEDURE
    	TR = TRIGGER
    	V = View',16,-1)
    	RETURN
END
/* SET wildcards ON END OF find value. */
SET @find = '%' + @find + '%'
/* Output object names which contain find value. */
SELECT DISTINCT OBJECT_NAME([id]) FROM syscomments
WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype = @type AND status >= 0) AND [text] LIKE @find

SQL Spatial test1

Thu, 09 Jul 2009 11:57:26 GMT

Description: STEnvelope, STCentroid, STBuffer, STConvexHull, STPointOnSurface

Link: http://www.codekeep.net/snippets/9aa518f8-36c2-47f5-be0f-ec5af66e29d9.aspx

DECLARE @g geometry
SET @g = geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 4, 3 4, 3 1, 0 1, 0 0))', 0)
SELECT @g
union all
SELECT @g.STEnvelope()
union all
select @g.STCentroid().STBuffer(.2)
union all
select @g.STConvexHull()
union all
select @g.STPointOnSurface().STBuffer(.2)
union all
select @g.STBuffer(-.5)
go

Truncate and Shrink Transaction Log for Database

Thu, 09 Jul 2009 04:12:44 GMT

Description: Tuncate the database log and shrink it.

Link: http://www.codekeep.net/snippets/27d08ac3-30e3-41c4-9512-8ee3c6b1d84a.aspx

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

MSSQL Database Mail: Resending Failed Mail

Wed, 01 Jul 2009 04:32:09 GMT

Description: Resends mail that has failed due to exceeding the specified retry attempts.

Link: http://www.codekeep.net/snippets/41a15075-29ea-4d7d-bb9f-6bd2eec45807.aspx

CREATE PROCEDURE sysmail_resend_failed
AS
BEGIN
SET NOCOUNT ON

DECLARE SYSMAIL_LOG_RESEND_CURSOR CURSOR READ_ONLY FOR
SELECT DISTINCT
l.mailitem_id
, p.name
, m.recipients
, m.subject
, m.body_format
, m.body
FROM msdb.dbo.sysmail_log l WITH (NOLOCK)
JOIN msdb.dbo.sysmail_mailitems m WITH (NOLOCK)
ON m.mailitem_id = l.mailitem_id
JOIN msdb.dbo.sysmail_profile p WITH (NOLOCK)
ON p.profile_id = m.profile_id
WHERE
l.event_type = 3
AND m.sent_status = 2
ORDER BY
l.mailitem_id

OPEN SYSMAIL_LOG_RESEND_CURSOR

WHILE (1=1) BEGIN
DECLARE
@mailitem_id int
, @profile_name nvarchar(128)
, @recipients varchar(max)
, @subject nvarchar(255)
, @body_format varchar(20)
, @body nvarchar(max)
FETCH NEXT FROM SYSMAIL_LOG_RESEND_CURSOR INTO
@mailitem_id
, @profile_name
, @recipients
, @subject
, @body_format
, @body
IF NOT @@FETCH_STATUS = 0 BEGIN
BREAK
END

PRINT CONVERT(varchar, GETDATE(), 121) + CHAR(9) + CONVERT(varchar, @mailitem_id) + CHAR(9) + @recipients

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name
, @recipients = @recipients
, @subject = @subject
, @body_format = @body_format
, @body = @body

UPDATE msdb.dbo.sysmail_mailitems
SET
sent_status = 3
WHERE
mailitem_id = @mailitem_id

END

CLOSE SYSMAIL_LOG_RESEND_CURSOR

DEALLOCATE SYSMAIL_LOG_RESEND_CURSOR

END
GO

Aggregate rows into comma delimited list

Thu, 25 Jun 2009 16:11:56 GMT

Description: single column of values converted to comma delimited list

Link: http://www.codekeep.net/snippets/a069cb63-5b29-4d38-ba7b-b8b8a2670cfd.aspx

SELECT field + ',' FROM table
FOR XML PATH('')

sql server 2005 / 2008 cheat sheet

Tue, 02 Jun 2009 17:59:06 GMT

Description: a simple cheat sheet with the most used code snippets for sql server 2005 / 2008

Link: http://www.codekeep.net/snippets/65cd9cfe-9fcb-47ed-b434-88e145f55fd4.aspx

                
--what was the name of the table with something like role                
                
SELECT * from sys.tables where [name] like '%dbName%'                
                
-- what are the columns of this table                 
                
select column_name , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH,  table_name  from Information_schema.columns where table_name='tbGui_ExecutedbName'                
                
                
-- find proc        
--what was the name of procedure with something like role                
                
select * from sys.procedures where [name] like '%ext%'                
exec sp_HelpText procName        
                
                
exec sp_helpText procUtils_InsertGenerator                
                
--how to list all databases in sql server                 
                
SELECT database_id AS ID, NULL AS ParentID, name AS Text FROM sys.databases ORDER BY [name]                
                
                
--HOW-TO LIST ALL TABLES IN A SQL SERVER 2005 DATABASE                
                
SELECT TABLE_NAME FROM [dbName].INFORMATION_SCHEMA.TABLES                
WHERE TABLE_TYPE = 'BASE TABLE'                  
AND TABLE_NAME <> 'dtproperties'                  
ORDER BY TABLE_NAME                
                
                
                
--HOW-TO ENABLE XP_CMDSHELL START                
-------------------------------------------------------------------------                
-- configure verbose mode temporarily                 
-- EXECUTE sp_configure 'show advanced options', 1                 
-- RECONFIGURE WITH OVERRIDE                 
--GO                 
                
                
--ENABLE xp_cmdshell                 
-- EXECUTE sp_configure 'xp_cmdshell', '1'                 
-- RECONFIGURE WITH OVERRIDE                 
-- EXEC SP_CONFIGURE 'show advanced option', '1';                 
-- SHOW THE CONFIGURATION                 
-- EXEC SP_CONFIGURE;                 
                
                
--turn show advance options off                 
-- GO                 
--EXECUTE sp_configure 'show advanced options', 0                 
-- RECONFIGURE WITH OVERRIDE                 
-- GO                
                
--HOW-TO ENABLE XP_CMDSHELL END                
-------------------------------------------------------------------------                
                
--HOW-TO IMPLEMENT SLEEP                 
-- sleep for 10 seconds                 
-- WAITFOR DELAY '00:00:10' SELECT * FROM My_Table                
                
 LIST ALL PRIMARY KEYS                 
                
SELECT                 
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS TABLE_NAME,                
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS COLUMN_NAME,                 
  REPLACE(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,' ', '_') AS CONSTRAINT_TYPE                 
FROM                 
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS                 
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON                 
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =                 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME                 
WHERE                 
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME <> N'sysdiagrams'                 
ORDER BY                 
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME ASC                
                
                
                
--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB                
--==================================================START                 
                
use dbName_Dev                
go                
drop table tbGui_LinksVisibility                
                
use dbName_test                
go                
select *                 
INTO [dbName_Dev].[ga].[tbGui_LinksVisibility]                
from [dbName_TEST].[ga].[tbGui_LinksVisibility]                
                
                
                
--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB                
--====================================================END                
--=================================================== SEE TABLE METADATA START                
                
                
                
                
SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], [value] AS                 
[DESCRIPTION] , c.max_length as [MAX_LENGTH] , c.is_nullable AS [OPTIONAL]                 
, c.is_identity AS [IS_PRIMARY_KEY] FROM sys.extended_properties AS ep                 
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id                 
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id                 
= c.column_id                 
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and                 
c.name = sc.column_name                 
WHERE class = 1 and t.name = 'tbGui_ExecutedbName' ORDER BY SC.DATA_TYPE                
                
                
                
--=================================================== SEE TABLE METADATA END               
                
select * from Information_schema.columns                
select table_name , column_name from Information_schema.columns where table_name='tbGui_Wizards'                
                
            
            
--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START                
                
                
SELECT T.name AS TableName, CAST(Props.value AS varchar(1000)) AS                
TableDescription                
FROM sys.tables AS T LEFT OUTER JOIN                
(SELECT class, class_desc, major_id, minor_id,                
name, value                
FROM sys.extended_properties                
WHERE (minor_id = 0) AND (class = 1)) AS                
Props ON T.object_id = Props.major_id                
WHERE (T.type = 'U') AND (T.name <> N'sysdiagrams')                
ORDER BY TableName                
                
--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START                
                
--=================================================== LIST ALL OBJECTS FROM DB START                
                
                
                
use DB                
--HOW-TO LIST ALL PROCEDURE IN A DATABASE                
select s.name from sysobjects s where type = 'P'                
--HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE                
select s.name from sysobjects s where type = 'TR'                
--HOW-TO LIST TABLES IN A DATABASE                 
select s.name from sysobjects s where type = 'U'                
--how-to list all system tables in a database                
select s.name from sysobjects s where type = 's'                
--how-to list all the views in a database                
select s.name from sysobjects s where type = 'v'                
                
                
                
                
                
Similarly you can find out other objects created by user, simple change type =                 
                
C = CHECK constraint                 
                
D = Default or DEFAULT constraint                 
                
F = FOREIGN KEY constraint                 
                
L = Log                 
                
FN = Scalar function                 
                
IF = In-lined table-function                 
                
P = Stored procedure                 
                
PK = PRIMARY KEY constraint (type is K)                 
                
RF = Replication filter stored procedure                
                
S = System table                 
                
TF = Table function                 
                
TR = Trigger                 
                
U = User table ( this is the one I discussed above in the example)                
    
UQ = UNIQUE constraint (type is K)                 
                
V = View                 
                
X = Extended stored procedure                
                
                
                
                
--=================================================== HOW-TO SEE ALL MY PERMISSIONS START                
                
                
                
                
SELECT * FROM fn_my_permissions(NULL, 'SERVER');                
USE dbName_qa;                
SELECT * FROM fn_my_permissions (NULL, 'database');                
GO                
           
                
--=================================================== HOW-TO SEE ALL MY PERMISSIONS END                
              
               
--find table               
              
use dbName_dev               
go               
select s.name from sysobjects s where type = 'u'  and s.name like '%Visibility%'              
select * from tbGui_LinksVisibility              
              
              
              
 find cursor              
              
use dbName      
go        
DECLARE @procName varchar(100)        
DECLARE @cursorProcNames CURSOR        
SET @cursorProcNames = CURSOR FOR        
select name from sys.procedures where modify_date > '2009-02-05 13:12:15.273' order by modify_date desc       
        
OPEN @cursorProcNames        
FETCH NEXT        
FROM @cursorProcNames INTO @procName        
WHILE @@FETCH_STATUS = 0        
BEGIN        
        
set nocount off;        
exec sp_HelpText @procName --- or print them        
-- print @procName        
        
FETCH NEXT        
FROM @cursorProcNames INTO @procName        
END        
CLOSE @cursorProcNames        
        
select @@error    
    
              
            
            
 --  SEE STORED PROCEDURE EXT PROPS            
            
SELECT ep.name as 'EXT_PROP_NAME' , SP.NAME , [value] as 'DESCRIPTION' FROM sys.extended_properties as ep left join sys.procedures as sp on sp.object_id = ep.major_id where sp.type='P'            
            
--  SEE STORED PROCEDURE EXT PROPS end

Search text inside stored procedure

Tue, 02 Jun 2009 10:38:35 GMT

Description: Get list of stored procedure, containing certain text in it

Link: http://www.codekeep.net/snippets/064c76b3-414e-4ac1-a91b-9c79a48c7f7d.aspx

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%text%' 
    AND ROUTINE_TYPE='PROCEDURE'

Set based looping

Fri, 29 May 2009 12:14:13 GMT

Description: set based looping

Link: http://www.codekeep.net/snippets/e8f75a0d-903f-4c8b-8b04-21c8df0ccaa0.aspx

--source:http://www.codeproject.com/KB/database/SetAndProceduralSQL.aspx
use Northwind


go

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products'
--Declare the Table variable 
DECLARE @Elements TABLE
(
        Number INT IDENTITY(1,1), --Auto incrementing Identity column
        ProductName VARCHAR(300) --The string value
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT 

--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

--Populate the TABLE variable using some logic
INSERT INTO @Elements SELECT ProductName FROM dbo.Products

--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1

--Determine the number of rows in the Table
SELECT @Count=max(Number) from @Elements

--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count

BEGIN
    --Load current value from the Table
    SELECT @CurrentValue = ProductName FROM @Elements WHERE Number = @CurrentDelimiterPositionVar
    --Process the current value
    print @CurrentValue
    --Increment loop counter
    SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END

Display SQL time formatts for sql server 2005 2008

Tue, 26 May 2009 10:56:43 GMT

Description: SOURCE:http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

Link: http://www.codekeep.net/snippets/b05a79e2-3985-471e-a0cc-8b00896c946b.aspx

USE tempdb; 
GO 
 
CREATE PROCEDURE dbo.help_DateTimeFormats 
    @styleID TINYINT = NULL 
AS 
BEGIN 
    SET NOCOUNT ON; 
     
    IF OBJECTPROPERTY 
    ( 
        OBJECT_ID('dbo.DateTimeFormats'), 
        'IsUserTable' 
    ) = 1 
    BEGIN 
        DROP TABLE dbo.DateTimeFormats; 
    END 
 
    CREATE TABLE dbo.DateTimeFormats  
    (  
        styleID TINYINT PRIMARY KEY,  
        outputLength TINYINT, 
        outputSyntax VARCHAR(64), 
        outputSample VARCHAR(255) 
    ); 
     
    INSERT dbo.DateTimeFormats(styleID, outputLength) 
        SELECT style = 0, outputLength = 19 
        UNION SELECT 1, 8  
        UNION SELECT 2, 8  
        UNION SELECT 3, 8  
        UNION SELECT 4, 8  
        UNION SELECT 5, 8  
        UNION SELECT 6, 9  
        UNION SELECT 7, 10  
        UNION SELECT 8, 8  
        UNION SELECT 9, 26  
        UNION SELECT 10, 8  
        UNION SELECT 11, 8  
        UNION SELECT 12, 6  
        UNION SELECT 13, 24  
        UNION SELECT 14, 12  
        UNION SELECT 20, 19  
        UNION SELECT 21, 23  
        UNION SELECT 22, 20  
        UNION SELECT 23, 10  
        UNION SELECT 24, 8  
        UNION SELECT 25, 23  
        UNION SELECT 100, 19  
        UNION SELECT 101, 10  
        UNION SELECT 102, 10  
        UNION SELECT 103, 10  
        UNION SELECT 104, 10  
        UNION SELECT 105, 10  
        UNION SELECT 106, 11  
        UNION SELECT 107, 12  
        UNION SELECT 108, 8  
        UNION SELECT 109, 26  
        UNION SELECT 110, 10  
        UNION SELECT 111, 10  
        UNION SELECT 112, 8  
        UNION SELECT 113, 24  
        UNION SELECT 114, 12  
        UNION SELECT 120, 19  
        UNION SELECT 121, 23 
        UNION SELECT 126, 23 
        UNION SELECT 130, 32 
        UNION SELECT 131, 25; 
 
    IF CHARINDEX('SQL Server 2005', @@VERSION) > 0  
        INSERT dbo.DateTimeFormats(styleID, outputLength) 
            SELECT 127, 23; -- 127 is new in 2005 
 
    UPDATE dbo.DateTimeFormats  
        SET outputSyntax = 'CONVERT(CHAR(' 
            + RTRIM(outputLength) + '), CURRENT_TIMESTAMP, ' 
            + RTRIM(styleID) + ')'; 
 
    DECLARE 
        @sql VARCHAR(1024), 
        @style TINYINT, 
        @syntax VARCHAR(64); 
 
    DECLARE c CURSOR 
        LOCAL FORWARD_ONLY STATIC READ_ONLY FOR  
        SELECT styleID, outputSyntax  
            FROM dbo.DateTimeFormats; 
             
    OPEN c; FETCH NEXT FROM c INTO @style, @syntax; 
 
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 
        SET @sql = 'UPDATE dbo.DateTimeFormats 
            SET outputSample = ' + @syntax + ' 
            WHERE styleID = ' + RTRIM(@style) + ';'; 
             
        EXEC(@sql); 
         
        FETCH NEXT FROM c INTO @style, @syntax; 
    END 
 
    CLOSE c; DEALLOCATE c; 
 
    SELECT 
        styleID, 
        outputSample, 
        outputSyntax 
    FROM 
        dbo.DateTimeFormats 
    WHERE 
        styleID = COALESCE(@styleID, styleID);  
 
    DROP TABLE dbo.DateTimeFormats;  
END 
GO 
 
EXEC dbo.help_DateTimeFormats; 
EXEC dbo.help_DateTimeFormats @styleID = 112; 
 
-- DROP PROCEDURE dbo.help_DateTimeFormats;

kill active connections to database

Mon, 25 May 2009 18:33:12 GMT

Description: kills all the active connections to a database

Link: http://www.codekeep.net/snippets/fb4980de-6af2-4d3a-8148-16abef6b2e22.aspx

print 'Killing active connections to the "' + 'OCMS_DEV' + '" database'
declare @execSql varchar(400)
-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = 'OCMS_DEV'
and
DBID <> 0
and
spid <> @@spid
exec (@execSql)

list all tables in a database

Sun, 24 May 2009 17:04:09 GMT

Description: List all tables in a database

Link: http://www.codekeep.net/snippets/25b814a0-22d8-4e15-bdd9-34f5a5888744.aspx

SELECT *
FROM INFORMATION_SCHEMA.Tables
WHERE table_schema = 'mydatabase';