| listdatabaseinfo.sql - Returns name, type, rows, number of indexes and other key information about the database |
| User-Defined string Functions Transact-SQL MS SQL Server - User-Defined string functions Transact-SQL GETWORDCOUNT, GETWORDNUM, AT, RAT, CHRTRAN, OCCURS, STRFILTER, PADC, PADR, PADL, PROPER, RCHARINDEX etc.
AT, PADL, PADR, CHRTRAN, PROPER are similar to functions Oracle PL/SQL INSTR, LPAD, RPAD, TRANSLATE, |
| Export To Excel File - This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls, by default). You can pass the server name, user name, user password, the select statement to execute, and the file |
| List Database ID - When using profiler it is nice to filter out the databases. This script gives two lists, one sorted by database name, the other by the ID. Copy this into a WORD doc for future reference. |
| fn_TablifyString2 - Breaks words in text string and returns as table with wordid, word and end position. Numerals and mixed alpha-num counted as words. |
| Pad Value - Function that will pad a value with a passed parameter. |
| sp_DisplayDBTriggers - Retrieves a list of all table triggers and related code in the selected database. I have only tested this on SQL Server 2000. It may work on other versions as well. |
| Trigger: checkForJobFail - We do not use SQL mail on our SQL servers. So, we developed this trigger on sysjobhistory to notify us of job failures.
We use xp_smtp_sendmail which is available free from http://www.sqldev.net/xp/xpsmtp.htm
|
| dba3_TriggerDemo_SystemUser_HostName_GetDate_Audit - Demonstrates a tamper resistant update audit trigger implementation suitable for single or multiple row updates (and insertions). (Deletion auditing is typically monitored using an additional table) |
| dba3_prc_DaysWeeksYearsInRange_Article - -- Determine the days weeks and years in a given range
-- Modeling Date Logic II: Queries, Functions, Procedures for Business Date & Time Calculations
-- http://www.sswug.org/see/17378 |
| View Active dbprocs - View all active dbprocs, who they belong to, and the query they are running on a requested database. This stored procedure is handy for troubleshooting server deadlocks and long running queries that may impact overall performance. |
| dba3_ResultSet_of_Objects_NotOwnedBy_dbo_demo - Display Objects not owned by dbo using system tables and inf schema views. |
| dba3_sys_LogCheckSumAggBinaryCheckSumCurrentValues_INSTALL - Procedures that provide a means for generating current bin agg checksum metrics for database tables. Changes in computed metric values over time are logged at the computation time in which they are detected to first be different from the most recent |
| dba3_fn_TableIdentityColumns_WithIdentCurrent_Article - UDF, returns identity column names With Ident Current values for a designated table in the same database. Requires a parameter @pTableName, of a specific table name. See: http://www.sswug.org/see.asp?s=1166&id=13833 |
| admin db - identify and track tables with no clustered indexes - In most cases you want a clustered index on all tables. The table maintained by this proc is a useful reference when working through those performance or deadlocking problems. |
| dba3_DeadLock_Connection_Demo_ay - Pubs deadlocking demo - creates a live deadlock when run with dba3_DeadLock_Connection_Demo_son.sql (adapted from developer training materials) Open DeadLock_Connection_Demo_ay.sql and DeadLock_Connection_Demo_son.sql in isqlw.exe in different connec |
| GetObjCreateDate - This stored procedure will scan a database for objects created
within the StartDate and EndDate and return their names and
creation date. |
| sp_rename_logical_filename.sql - Used to change logical file name of db.....create on db needing change. Run proc once for each file name(once for each data file, once for each log file).
After running proc, detach/attach db also(to avoid other problems later) |
| dba3_NorthWind_0040_Materialized_Object_Invoices_Article - View it now! - Part II: Banishing view timeouts by implementing user "materialized views" |
| dba3_prc_BusinessDaysInRangeByComputation_Article - -- Determine the number of business days in a given timeframe
-- by computation
-- Modeling Date Logic II: Queries, Functions, Procedures for Business Date & Time Calculations
-- http://www.sswug.org/see/17378
|
| create table from insert - Create table from insert |
| Get unique records and their count from a table - This script will get an aggregate of a given value and the number of times it appears in the table.
Such as:
Value1 15
Value2 22 |
| Generate Scripts to Disable and Enable Constraints - This VBScript uses DMO to create two scripts, one to drop/disable foreign key constraints and another to recreate them. Comes in handy for quickly moving data between databases with bulkcopy and you don't want to worry about dependencies. (hack?) |
| AutomaticMechanismsCanBeSurprising.sql - Helper script for AutomaticMechanismsCanBeSurprising article |
| admin db - sysperfinfo size and performance trend analysis - You can use perfmon and muck around with many files and the perfmon gui, or you can use this data capture procedure and the SQL table sysperfinfo to easily examine trends from a single table. Included are some examples of ways to slice and dice the d |
| dba3_SpecifyRandNumberSize_via_Power - A random number generator that
will allow the input of the number of digits. |
| dba3_ValueInsertionScripts_demo - A demonstration of how to generate Value insertion scripts. |
| ChangeAllObjOwner - This stored procedure can be used to run through all of a specific
database's objects owned by the 'oldowner' and change the old
owner with the new one.
You should pass the old owner name and the new owner name,
as in the example below:
E |
| GetSPTrigValues - This stored procedure can be used to return the names of each view,
rule, default, trigger, CHECK constraint, DEFAULT constraint, and
stored procedure, which contain the particular text value. |
| Flag only the duplicate Rows - Flag duplicate rows, leaving the 1st row one of each kind not flagged |
