Multi-statement Table Valued Function vs Inline Table Valued Function

remove orphan users

--create proc usp_remove_orphan_users as
set nocount on

-- Section 1: Create temporary table to hold databases to process

-- drop table if it already exists
if (select object_id('tempdb..#dbnames')) is not null
drop table #dbnames

-- Create table to hold databases to process
create table #dbnames (dbname varchar(128))

-- Section 2: Determine what databases have orphan users
exec master.dbo.sp_MSforeachdb 'insert into #dbnames select ''?'' from master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'')
having count(*) > 0'

-- Section 3: Create local variables needed
declare @CNT int
declare @name char(128)
declare @sid varbinary(85)
declare @cmd nchar(4000)
declare @c int
declare @hexnum char(100)
declare @db varchar(100)

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from #DBNAMES
While @CNT > 0
begin

-- get the name of the top database
select top 1 @db=dbname from #DBNAMES

-- delete top database
delete from #DBNAMES where dbname = @db

-- Build and execute command to determine if DBO is not mapped to login
set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid' +
' where l.sid is null and u.name = ''DBO'''
exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
if @cnt = 1
begin
print 'exec ' + @db + '..sp_changedbowner ''SA'''
-- exec sp_changedbowner 'SA'
end -- if @cnt = 1


-- drop table if it already exists
if (select object_id('tempdb..#orphans')) is not null
drop table #orphans

-- Create table to hold orphan users
create table #orphans (orphan varchar(128))

-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
set @cmd = 'insert into #orphans select u.name from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid ' +
'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +
'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' +
'and u.name <> ''system_function_schema'')'
exec (@cmd)


-- Are there orphans
select @cnt = count(*) from #orphans

WHILE @cnt > 0
BEGIN

-- get top orphan
select top 1 @name= orphan from #orphans

-- delete top orphan
delete from #orphans where orphan = @name

-- Build command to drop user from database.
set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
print @cmd
--exec (@cmd)


-- are there orphans left
select @cnt = count(*) from #orphans
end -- WHILE @cnt > 0


-- are the still databases to process
select @cnt=count(*) from #dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table #dbnames, #orphans

SQL Server DATE/TIME Workbench

Reviewing AutoGrow events from the default trace

DECLARE
@path VARCHAR(255),
@cmd VARCHAR(300);

-- customize this path, of course, if necessary:
SELECT
@path = 'C:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\',
@cmd = 'dir /b "' + @path + '*.trc"';

DECLARE @files TABLE
(
fn VARCHAR(64)
);

INSERT @files
EXEC master..xp_cmdshell @cmd;

DELETE @files
WHERE fn IS NULL;

SELECT
e.DatabaseName,
e.[FileName],
e.SPID,
e.Duration,
e.StartTime,
e.EndTime,
FileType = CASE e.EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END,
[TraceFile] = f.fn
FROM
@files f
CROSS APPLY
fn_trace_gettable(@path + f.fn, DEFAULT) e
WHERE
e.EventClass IN (92,93)
-- AND f.fn LIKE 'log[_][0-9]%.trc'
ORDER BY
e.StartTime DESC;

Excel - if a cell value exist in list on other column list

=IF(ISNA(VLOOKUP(A15,$E$1:$E$600,1,FALSE)),"",A15)

Cleanup duplicate record, another interesting method

SET ROWCOUNT 1
AGAIN:
DELETE member_mlc FROM
(SELECT memberid,mlc_status FROM member_mlc GROUP BY memberid,mlc_status HAVING COUNT(*) > 1) a
WHERE member_mlc.memberid = a.memberid and member_mlc.mlc_status = a.mlc_status
IF @@ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0

Wildcard search characters in SQL

underscore is a wildcard for single charachter

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '_a_a%' order by TABLE_NAME
ces_barclaysplc dbo data_type BASE TABLE
ces_barclaysplc dbo data_type_group BASE TABLE
ces_barclaysplc dbo data_type_group_operator BASE TABLE
ces_barclaysplc dbo datafeed_questionbank BASE TABLE
ces_barclaysplc dbo datafeed_questionbank_BAKUP BASE TABLE
ces_barclaysplc dbo datafeed_testanswer BASE TABLE
ces_barclaysplc dbo datafeed_testbank BASE TABLE
ces_barclaysplc dbo datafeed_testquestion BASE TABLE


to search for underscore use [_]

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '[___]%' order by TABLE_NAME
ces_barclaysplc dbo ___analytics_report_schedule_08_26_2009 BASE TABLE
ces_barclaysplc dbo ___datafeed_talent_20100210 BASE TABLE
ces_barclaysplc dbo ___datafeed_users_20100210 BASE TABLE
ces_barclaysplc dbo ___dataseg_ucf BASE TABLE
ces_barclaysplc dbo ___lo_options BASE TABLE
ces_barclaysplc dbo ___loadrl BASE TABLE
ces_barclaysplc dbo ___pdOnline_20100210 BASE TABLE

Show statistics and query plans

SET SHOWPLAN_ALL on
go
set statistics IO on
set statistics time on


.....QUERY IS HERE

GO

SET SHOWPLAN_ALL off
go

Resource Governor in SQL Server 2008 - Sample

http://www.sql-server-performance.com/articles/per/Resource_Governor_in_SQL_Server_2008_p1.aspx

VBScripts to manage Text Files

http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/other/textfiles/