Data Layer Class Generator

I can’t remember where I found it, but this SQL script will generate a Dapper compatible class file for the table you specify.

Also this script is handy to use in the business / mapping layer of you application:

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
SO.name AS [ObjectName],
REPLACE(p.name, '@', 'rec.') + ',' as prop,
SO.Type_Desc AS [ObjectType (UDF/SP)],
P.parameter_id AS [ParameterID],
P.name AS [ParameterName],
TYPE_NAME(P.user_type_id) AS [ParameterDataType],
P.max_length AS [ParameterMaxBytes],
P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P 
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN'))
AND SO.name = 'YourProcedureSave'
ORDER BY [Schema], SO.name, P.parameter_id

Enhanced Stored Procedure Generator

Take the tedium out of coding CRUD stored procedures and use this enhanced code generator crud_generator instead.  It’s the one from www.sqlbook.com with the following enhancements:

1) Suffixes changed to Search, Load, Save and Delete
2) Generates a comment header for each procedure
3) Generates default values when null parameters supplied to Save procedure
4) Uses char(13) + char(10) for new lines

This is a great way to jump start your coding after your data model is finalised.  Then use Dapper to build your c# data access layer.  Job done.

Generate All Grants in a Database

Use this handy SQL to generate all grants that exist in the current database.

SELECT
 (
 dp.state_desc + ' ' +
 dp.permission_name collate latin1_general_cs_as + 
 ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
 ' TO ' + '[' + dpr.name + ']'
 ) AS GRANT_STMT
FROM sys.database_permissions AS dp
 INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
 INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
 INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('guest')

Find Agent Jobs Containing a String

Use this handy query to find all agent jobs that have steps containing a search string.

DECLARE @Search varchar(255)
SET @Search='usp_YourStoredProc'
 
SELECT j.name, j.enabled, s.step_id, s.database_name, s.subsystem, s.command
FROM msdb.dbo.sysjobsteps s
INNER JOIN msdb.dbo.sysjobs j ON s.job_id = j.job_id
WHERE command LIKE '%'+@Search+'%'
ORDER BY 1, 3

Thanks to this StackOverflow post for pointing me in the right direction.

SQL Server Autofix Logins

I need these commands all the time when restoring databases. This will lists the orphaned users:

 EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

 EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

 EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Hat tip here.

Find a String in a Stored Procedure

Use this handy query to find all procedures containing the search string @Search

DECLARE @Search varchar(255)
SET @Search='Document'

SELECT DISTINCT
 o.name AS Object_Name,o.type_desc
 FROM sys.sql_modules m 
 INNER JOIN sys.objects o ON m.object_id=o.object_id
 WHERE m.definition Like '%'+@Search+'%'
 ORDER BY 2,1

Thanks to StackOverflow for this. Or alternatively make use of the information_schema:

SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION 
 FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_DEFINITION LIKE '%Document%'

Hat tip here.