Change Schema owner of objects in DB

use ces_AUTOMATION_LD4PILOT
go

declare @name nvarchar(1000), @sql nvarchar(4000)
------------------------
--rename schem onwer for users
declare tmp_cur2 cursor for
SELECT 'ALTER SCHEMA [DBO] TRANSFER [' + s.Name + '].' + o.Name
    FROM sys.Objects o
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
    WHERE s.name = 'ToBeDeleted'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
   
open tmp_cur2
Fetch next from tmp_cur2 into @sql

while @@fetch_status = 0
begin
print @sql
exec sp_executesql @sql
Fetch next from tmp_cur2 into @sql
print '---'
end

close tmp_cur2
deallocate tmp_cur2          

drop orphan users and rename current objects schema

CREATE SCHEMA [ToBeDeleted]
go


declare @name nvarchar(1000), @sql nvarchar(4000)
declare tmp_cur cursor for
SELECT     u.name --u.*--, l.*
FROM    sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name


open tmp_cur
Fetch next from tmp_cur into @name

while @@fetch_status = 0
begin
    print @name
    set @sql = ''

    ------------------------
    --rename schem onwer for users
    declare tmp_cur2 cursor for
    SELECT 'ALTER SCHEMA [ToBeDeleted] TRANSFER [' + s.Name + '].' + o.Name
        FROM sys.Objects o
        INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
        WHERE s.Name = @name
        And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
       
    open tmp_cur2
    Fetch next from tmp_cur2 into @sql

    while @@fetch_status = 0
    begin
    print @sql
    exec sp_executesql @sql
    Fetch next from tmp_cur2 into @sql
    print '---'
    end

    close tmp_cur2
    deallocate tmp_cur2   


    ------------------------
    --drop users and schema

    set @sql = 'DROP SCHEMA ['+@name+']'
    print @sql
    exec sp_executesql @sql

    set @sql = 'DROP USER ['+@name+']'
    print @sql
    exec sp_executesql @sql
    ------------------------

    Fetch next from tmp_cur into @name
    print '---'
end

close tmp_cur
deallocate tmp_cur

-------------------------------------------------------------------------

List of inactive users in a DB

SELECT     u.*, l.*
FROM    sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name

In SSIS how to continue a for each loop container even if its inner tasks fails (Usage of Propogate Property in SSIS)

http://www.codeproject.com/Articles/384690/In-SSIS-how-to-continue-a-for-each-loop-container

Recompile Stuck Procs : sp_recompile_stuck_procs

USE [master]
GO
/*
exec sp_recompile_stuck_procs 2
*/
CREATE PROCEDURE [dbo].[sp_recompile_stuck_procs]
(
@maxcount int = 10
)
AS
BEGIN
    SET NOCOUNT ON;


declare @datestr nvarchar(100), @sql nvarchar(4000), @tblname nvarchar(100)
--declare @maxcount int = 10
select @datestr = CONVERT(nvarchar(10),getdate(), 112)+'_'+CONVERT(nvarchar(10),datepart(hour,getdate()))+'_'+CONVERT(nvarchar(10),datepart(minute,getdate()))+'_'+CONVERT(nvarchar(10),datepart(SECOND,getdate()))
--select @datestr
set @tblname = '__drop_who3_collection_1_minute_'+@datestr

set @sql = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tblname+']'') AND type in (N''U''))
create table '+@tblname+'
(SPID bigint,Status varchar(100),Login varchar(100),Host varchar(100),BlkBy bigint, TempDBWait varchar(100),DBName varchar(100),CommandType varchar(100),
SQLStatement varchar(max),ObjectName varchar(100),ElapsedMS bigint,CPUTime bigint,IOReads bigint,IOWrites bigint,LastWaitType varchar(100),
StartTime datetime,Protocol varchar(100),ConnectionWrites bigint,ConnectionReads bigint,ClientAddress  varchar(100),Authentication  varchar(100), Collectiontime datetime)
'
--print @sql
exec sp_executesql @sql   

set @sql = 'insert into '+@tblname+'
exec sp_who3 '
--print @sql
exec sp_executesql @sql   

set @sql = 'select dbname, objectname, count(*) as cc into '+@tblname+'_loop from '+@tblname+' where objectname is not null group by  dbname, objectname having count(*) > '+CONVERT(varchar, @maxcount)
--print @sql
exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname
----print @sql
--exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname+'_loop'
----print @sql
--exec sp_executesql @sql   


---------------------------------------------------------
--declare @tblname nvarchar(100) = '__drop_who3_collection_1_minute_20120917_13_37_16' , @sql nvarchar(1000)
declare @tbl table (db nvarchar(100) , sp nvarchar(100), cc int)
declare @db nvarchar(100) , @sp nvarchar(100)

set @sql = 'select dbname, objectname, cc From '+@tblname+'_loop'

insert into @tbl
exec sp_executesql @sql

declare tmp_cur cursor for
select db, sp From @tbl

open tmp_cur
Fetch next from tmp_cur into @db, @sp

while @@fetch_status = 0
begin
    --print  @db
    --print @sp
    set @sql = 'use ' +@db +'
    exec sp_recompile '+replace(@sp, 'dbo.','')

    print @sql
    --exec sp_executesql @sql


Fetch next from tmp_cur into  @db, @sp
print '---'
end

close tmp_cur
deallocate tmp_cur

-----------------------------------
if (select COUNT(*) from @tbl) > 0
begin

DECLARE @tableHTML  NVARCHAR(MAX) ='...' , @subjectstring nvarchar(200)='...'

SET @tableHTML =
    N'

Proc Re-compile on '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N' ' +
    CAST ( ( SELECT td = db,       '',
                    td = sp,       '',
                    td = cc
              FROM @tbl
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Database	Proc	Counts

' ;
   
    print @tableHTML
   
set @subjectstring = 'Alert : Proc Re-compile on SQL Server '+@@SERVERNAME
print @subjectstring

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'default',
            @recipients ='patrick@csod.com',
            @body = @tableHTML,
            @body_format = 'HTML' ,
            @subject = @subjectstring;
           
end

---------------------------------------

set @sql = 'drop table '+@tblname+'_loop'
--print @sql
exec sp_executesql @sql   



set @sql = 'drop table '+@tblname
--print @sql
exec sp_executesql @sql   


END

determine last time file growth happened

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/662f4e65-0091-49e6-a497-ec23a8bb1ca2/

Reindex Online tables - maintenance_re_index

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
    @MaxFragmentation int = 10 ,
    @TrivialPageCount int = 100 ,
    @RebuildThreshold int = 40
)
as
BEGIN

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;

--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;

---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
    DROP TABLE #work_to_do;

SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)
    BEGIN;
        FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
            SET @HasBlobColumn = 0 -- reinitialize
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas AS s ON s.schema_id = o.schema_id  WHERE o.object_id = @objectid;
           
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE object_id = @objectid AND index_id = @indexid;

            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;

            -- Check for BLOB columns
            IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
                SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
                FROM sys.objects SO
                inner join sys.columns SC ON SO.Object_id = SC.object_id
                inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
                (
                    ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
                    OR
                    ST.name IN ('text', 'ntext', 'image', 'xml')
                )
                WHERE SO.Object_ID = @objectID
            ELSE -- nonclustered. Only need to check if indexed column is a BLOB
                SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
                FROM sys.objects SO
                INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
                INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
                INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
                INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
                (
                    ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
                    OR
                    ST.name IN ('text', 'ntext', 'image', 'xml')
                )
                WHERE SO.Object_ID = @objectID
           
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
                IF @frag > @RebuildThreshold
                BEGIN
                    SET @command = @command + N' REBUILD'
                    IF @HasBlobColumn = 1
                        SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
                    ELSE
                        SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
                END
                ELSE
                    SET @command = @command + N' REORGANIZE'
                    IF @partitioncount > 1
                        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
                        PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
                       
                        --PRINT  @command
                        --print 'print '''+@command +''''
                        --print 'Go'
                        EXEC (@command)
        END;
        -- Close and deallocate the cursor.
        CLOSE partitions;
        DEALLOCATE partitions;
END

parent child relation hierachy script

with cte as
(
select 1 as lvl, SpaceShuttleID , ParentSpaceShuttleID from SpaceShuttle where SpaceShuttleID = 200
union all
select cte.lvl + 1, b.SpaceShuttleID , b.ParentSpaceShuttleID  from SpaceShuttle b join cte on cte.SpaceShuttleID = b.ParentSpaceShuttleID
)
select * from cte order by lvl;

modify sysjobs trigger to send alert

USE [msdb]
GO
/****** Object:  Trigger [dbo].[trig_sysjobs_insert_update]    Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON
  -- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view. 
  IF (EXISTS (SELECT *
            FROM inserted
           WHERE inserted.originating_server_id NOT IN
                    (SELECT v.originating_server_id
                     FROM sysoriginatingservers_view AS v)))
  BEGIN
   RAISERROR(14379, -1, -1, 'dbo.sysjobs')
   ROLLBACK TRANSACTION
    RETURN
  END
  else
  begin

    Declare @Body1 nvarchar(max)
    Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = 'palexander@csod.com',
    @body = @Body1,
    @subject = 'SQL Server Job Modification Alert'

  end
END

SQL job queries

--JOB INFO
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'


--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

 --JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'