Script to auto generate a security report for your SQL Server instance

RESEED IDENTITY Column

DBCC CHECKIDENT (orders, NORESEED)
DBCC CHECKIDENT (orders, RESEED, 999)

Automated Trace File Collection

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = '2009-07-10 21:00:00.000'
set @DateTime = dateadd(mi,2,GETDATE())
--select @DateTime
set @maxfilesize = 20

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
declare @filename nvarchar(100)
set @filename = N'C:\SQL_PROFILER\SQL01-mnmember1'
exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 7
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - de04e276-c5f5-4a14-a12c-d8f42f0c5a8b'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go


/*
SELECT * FROM :: fn_trace_getinfo(default)


-- Set the trace status to start
exec sp_trace_setstatus 2, 1
-- stops the trace
exec sp_trace_setstatus 2, 0
-- closes the trace and deletes its definition from the server
WAITFOR DELAY '00:00:30'
exec sp_trace_setstatus 2, 2

*/

A simple Cursor sample

declare @mid int , @comid int
declare tmp_cur cursor for
select distinct memberid, communityid from _cleanup_dups order by memberid

open tmp_cur
Fetch next from tmp_cur into @mid, @comid

while @@fetch_status = 0
begin
print @mid
print @comid
Fetch next from tmp_cur into @mid, @comid
print '---'
end

close tmp_cur
deallocate tmp_cur

Sending scheduled SQL query results via HTML e-mail using SSIS

Sending email from SQL using sp_send_dbmail

declare @sub nvarchar(1000)
set @sub = 'Engage Daily Import Counts for: '+Convert(nvarchar(100),getdate(),101)

exec msdb.dbo.sp_send_dbmail
@Subject=@sub,
@query = '
set nocount on;
--select "Engage Import Results for: "+Convert(nvarchar(100),getdate(),101);
select count(*) as Users from PstgreSQL.dbo.users;
print "
";
select COUNT(*) as PostalCode from PstgreSQL.dbo.PostalCode;
print "
";
select COUNT(*) as Users_stage from PstgreSQL.dbo.Users_stage;
print "
";
select COUNT(*) as PostalCode_stage from PstgreSQL.dbo.PostalCode_stage;
',
@recipients = 'palexander@spark.net',
@body_format = 'HTML'


--select 'Import Results for: '+Convert(nvarchar(100),getdate(),101);

Automating Performance Monitor Statistics Collection for SQL Server and Windows

Saving and Retrieving File Using FileStream SQL Server 2008

Find a Table or view within entire SQL server

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.tables
WHERE table_NAME = ''vw_fctmemberdetail_ca''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' +table_CATALOG + '']'', ''['' + table_NAME + '']''
FROM [?].INFORMATION_SCHEMA.tables
WHERE table_NAME = ''vw_fctmemberdetail_ca'';
END';

Rob's SQL Server Blog

Rob's SQL Server Blog


Helpful Blog