Searching every stored procedure through sys.sql_modules finds text quickly, but comments, case, dynamic SQL, and other databases need care.
SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length
SQL Server 2005 has no VARCHAR(12500), so how do you cap a VARCHAR(MAX) field at 12500 characters? A constraint on the column does the job.
SQL SERVER – Retrieve Information of SQL Server Agent Jobs
sp_help_job returns information about SQL Server Agent jobs in msdb. Run it with no parameters to list every job, plus a few related Agent posts.
SQL SERVER – Trace Flags – DBCC TRACEON
Trace flags let a DBA turn a database function on or off for a while. How to use DBCC TRACEON and TRACEOFF, with deadlock trace flag 1205 as the example.
SQL SERVER – Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table
Msg 544, cannot insert explicit value for identity column, happens when IDENTITY_INSERT is OFF. Turn it ON, insert your rows, then turn it OFF again.
SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility
Use sp_dbcmptlevel to change the database compatible level, like 80 for SQL Server 2000 or 90 for 2005, plus the three step best practice to do it.
CTE Column Names: Rename Positions Without Moving Values
CTE column names rename positions without moving values. Compare inner aliases, report names and a misleading reversed list.