SQL SERVER - Guidelines and Coding Standards

Find a Stored Procedure within entire server

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''up_AggregateStatistics_SingleDate''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' + SPECIFIC_CATALOG + '']'', ''['' + ROUTINE_NAME + '']''
FROM [?].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''up_AggregateStatistics_SingleDate'';
END';

Change Job Owner

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'9b72dfd8-0845-4632-8ffb-43d35e723ace', @owner_login_name=N'sa'
GO

--select * from msdb.dbo.sysjobs where job_id=N'9b72dfd8-0845-4632-8ffb-43d35e723ace'

Purge MSDB Mail History

use msdb
go


DECLARE @d datetime
select @d = min(last_mod_date) from sysmail_attachments with (nolock)
select @d
set @d = dateadd(dd,1,@d)
select @d

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d
go

DBCC SHRINKFILE (N'MSDBLog' , 0)
GO
DBCC SHRINKDATABASE(N'msdb' )
GO

/*
select top 10 * from sysmail_attachments with (nolock)
select count(*) from sysmail_attachments with (nolock)
select min(last_mod_date) from sysmail_attachments with (nolock)
select max(last_mod_date) from sysmail_attachments with (nolock)
*/

Grant Permission to View Stored Procedures - Dynamic Creation for all DBs

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)''
print ''set @user = ''''SPARKSTAGE\developers''''''
print ''DECLARE Cur_name CURSOR FOR ''
print ''select specific_name From information_Schema.routines''
print ''OPEN Cur_name''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''WHILE @@FETCH_STATUS = 0''
print ''BEGIN''
print ''set @string =''''GRANT VIEW DEFINITION ON [dbo].[''''+@name+''''] TO [''''+@user+'''']''''''
print ''print @string''
print ''exec sp_executesql @string''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''END''
print ''CLOSE Cur_name''
print ''DEALLOCATE Cur_name''
print ''go''
end
'
go

Grant View Permission on Stored Procedures

--select * from information_Schema.routines
--select specific_name from information_Schema.routines

declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'SPARKSTAGE\developers'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name

Extract Text after 2 commas in a string

select MemberGroupLangStr
, CHARINDEX(',',MemberGroupLangStr)
, substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))
, CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, substring(MemberGroupLangStr , CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))) + 1 , len(MemberGroupLangStr))
From MembersWithUnapprovedTextAttributes

Extract Text between two commas

select MemberGroupLangStr,
CASE
-- when no second semi-colon,
WHEN CHARINDEX(',',(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99))) = 0
-- then extract the full string from first semi-colon to the max length of 99
THEN LTRIM(RTRIM(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99)))
-- else extract the full string from the first semi-colon up to second semi-colon
ELSE LTRIM(RTRIM(SUBSTRING(
/* > */ MemberGroupLangStr,
/* */ CHARINDEX(',',MemberGroupLangStr,1) + 1,
/* */ CHARINDEX(',', SUBSTRING(MemberGroupLangStr,
CHARINDEX(',', MemberGroupLangStr, 1) + 1,
99)) - 1
) ) )
END AS Result_string
From MembersWithUnapprovedTextAttributes

Grant access to database in a server for domain user

EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''CREATE USER [Domain\user] FOR LOGIN [Domain\user]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''Domain\user''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''Domain\user''''''
print ''go''

end
'
go

List objects per filegroup

IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL

DROP TABLE #FileGroup



IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL

DROP TABLE #ObjectFileGroup



CREATE TABLE #FileGroup (

FileGroup sysname

)

CREATE TABLE #ObjectFileGroup (

ObjectName sysname,

ObjectType varchar(20),

FileGroupID int,

FileGroup sysname

)



SET NOCOUNT ON



DECLARE @TableName sysname

DECLARE @id int



DECLARE cur_Tables CURSOR FAST_FORWARD FOR

SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'

OPEN cur_Tables

FETCH NEXT FROM cur_Tables INTO @TableName, @id

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #FileGroup

INSERT #FileGroup (FileGroup)

EXEC sp_objectfilegroup @id

INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup

FROM #FileGroup

FETCH NEXT FROM cur_Tables INTO @TableName, @id

END



CLOSE cur_Tables

DEALLOCATE cur_Tables



INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes

WHERE FILEGROUP_NAME(groupid) IS NOT NULL

AND OBJECT_NAME(id) NOT LIKE 'sys%'

AND [name] NOT LIKE '_WA_Sys%'

AND [name] NOT LIKE 'Statistic_%'



SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128

FROM #ObjectFileGroup ofg

RIGHT JOIN dbo.sysfiles sf

ON ofg.FileGroupID = sf.groupid

ORDER BY FileGroup, ObjectName



SQL 2005 (very simplified)



SELECT

fg.data_space_id, fg.name,

ObjectName = OBJECT_NAME(p.object_id), p.index_id

,df.name, df.physical_name, [Size] = df.size*8/1024

FROM sys.filegroups fg

LEFT JOIN sys.database_files df

ON fg.data_space_id = df.data_space_id

LEFT JOIN sys.partitions p

ON fg.data_space_id = p.partition_number

WHERE (p.object_id>4096 or p.object_id IS NULL)