http://www.sqlcommunity.comMicrosoft SQL Server Communityen-USCopyright 2007 by www.sqlcommunity.comsqlgurus@sqlcommunity.com This Script is to display the Tables present in a database and also on which schema,FileGroup the t...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxWed, 24 Dec 2008 10:01:41 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=386 This Script list all indexes present on all tables present in all schemas in a database.   ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxWed, 24 Dec 2008 09:56:13 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=385 This script will help us to obtain all the table's info in a DB. It gives us the information on num...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxTue, 23 Dec 2008 04:56:33 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=384 This scripts gives you the blocked process report with the leading blockers and what queries are ru...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 18 Dec 2008 16:29:00 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=382This Script is to list the indexes present on all tables in a given Database Syntax: ListallTableInd...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxWed, 01 Oct 2008 17:37:12 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=346 The script helps us identify all tables in DB that have Unique Constraint defined on them. http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 17:02:02 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=341 The script helps us identify all tables in DB that do not have index(s) defined on them. http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:59:04 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=340 This Script helps us to identify all table(s) in DB that do not have Clustered Index defined on it....http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:54:57 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=339 The script helps us to Identify Tables in DB Without Primary Key,but has unique constraint http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:50:01 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=338 The script helps to Identify Tables in DB that have Clustered Index,but it is not built on Primary ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:46:43 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=337 The Script helps us identify all table(s) that do not have Primary Key defined on them. http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:31:43 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=336 The script helps to fetch all tables in the database that have their Primary key(s) defined as Non ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:28:32 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=335 The Script helps to fetch all tables that have Clustered Index defined on it, but has no primary ke...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 05 Sep 2008 16:26:04 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=334 This script will generate a script to create all secondary indexes for a database.  The output is f...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSat, 05 Jul 2008 18:32:21 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=306 /*SELECT --f.name AS foreign_key_name OBJECT_NAME(f.parent_object_id) AS 'f.parent_object_id' ,...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 22 Jun 2008 11:16:38 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=300 This script automatically starts a trace and stores the trace data in to a table and then analyzes ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 12 Jun 2008 02:11:52 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=296 Querying Mirroring Connections Sometimes you may have to look into current mirroring connections...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 04 Apr 2008 07:54:28 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=233 --Check current owners SELECT name, description, suser_sname(ownersid) FROM msdb.dbo.sysdtspac...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 27 Mar 2008 15:00:36 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=228 --Change to active database name --USE database_name --go DECLARE @UserName varchar(50) DE...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 27 Mar 2008 14:57:02 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=227-- Author: Kalen Delaney (www.SQLTuners.net) -- Date: March 10th 2008 -- Description: This view con...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxTue, 11 Mar 2008 01:16:36 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=207 declare @ts_now bigint @ts_now = ms_ticks from sys.dm_os_sys_info * ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxMon, 03 Mar 2008 19:51:55 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=195 This script will allow you to find out which object (table or index or heap) in which filegroup/fil...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxWed, 27 Feb 2008 23:25:05 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=189 Generates a detailed SQL login permissions report.  Compatible with SQL2000 and up.  Please see the...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxMon, 25 Feb 2008 19:08:27 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=188 I have made a solution for backup, integrity check and index optimization in SQL Server 2005 and SQ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSat, 16 Feb 2008 15:52:45 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=181 Script to identify databases that have no full or transaction log backups, either today or yesterda...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxTue, 12 Feb 2008 15:34:41 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=174 Script to identify executing Sql Server Agent jobs and step names.  Uses an extended stored procedu...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxTue, 12 Feb 2008 15:23:27 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=173 There are many cases when DBA wants to query all of his SQL Servers in farm or deploy a server scri...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSat, 09 Feb 2008 19:59:57 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=167 -- GET DATABASE BACKUP HISTORY -- Louis Nguyen 2008 -- IF Disk Backup, b.physical_device_name w...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 01 Feb 2008 19:29:45 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=140 This script will take any table from any database and create an audit table alongside with the name...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 18 Jan 2008 16:58:53 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=124The purpose of this script is to drop all foreign key relationships within your database so you can ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 17 Jan 2008 10:01:51 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=119 This script detects and displays orphan SQL Server accounts from all the databases of your local SQ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxMon, 26 Nov 2007 11:02:41 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=102 This script checks if your local SQL Server is configured with /3GB switch in pagefile.sys file or ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxMon, 26 Nov 2007 10:59:25 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=101   Displays the number of rows in the table, Total amount of space reserved for th...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxFri, 26 Oct 2007 08:25:57 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=91 Having always avoided maint plans and the dreaded sysmaint.exe I've created my own procs to handle ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxTue, 16 Oct 2007 15:42:04 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=81 Script to find out which login has what server role permissions?   Many a times DBA’s ma...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxMon, 15 Oct 2007 10:03:37 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=77 To get the current date and time of a system following Date Time Functions can be used. ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxMon, 15 Oct 2007 06:53:52 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=73    Find out what's happening in TempDB database This script shows you what's happening beh...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 11 Oct 2007 07:24:58 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=65 Change the DTS Package owner using SQL_ChangeDTSOwner Author: Saleem Hakani Thi...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSat, 06 Oct 2007 23:45:57 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=54 Get the facts about your drives and SQL Server space utilization from SQL Server Author: S...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSat, 06 Oct 2007 23:35:05 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=53 Get detils of your databases using SQL_DBReport Author: Saleem Hakani SQL_D...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSat, 06 Oct 2007 23:09:09 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=52 Get the Facts about your SQL Server Backups Author: Saleem Hakani This script...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 04 Oct 2007 20:51:04 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=50 Get the facts on data/log usage from each drive. Author: Saleem Hakani SQL_Dri...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 04 Oct 2007 20:13:49 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=49 Backup all User and System Databases excluding Mirror Databases Author: Saleem Hakani ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 04 Oct 2007 10:13:13 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=47 This procedure can be scheduled to run on any frequency and it will go out and look at the sqlagent...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxThu, 27 Sep 2007 19:48:11 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=38 This scipt helps you unrestrict the size of your data and log files and sets the filegrowth to 10%....http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 23:33:45 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=16 This script helps in finding out how many records were inserted/updated/deleted since the last time...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 23:31:36 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=15 SQL_SmartINDEX allows you to quickly get the facts on how much percentage of fragmentation you h...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 23:27:30 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=14 SQL_ShowLOCKS: This script helps you detect realtime deaclocks and blocks from your SQL Server 20...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 23:22:25 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=13 SQL_ReIndeXER25 allows you to rebuild indexes (ONLINE) from a single database to all the databas...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 23:19:06 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=12 With the release of SQL Server 2005 SP1, Microsoft released a new High Availability technology cal...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 23:15:57 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=11 SQL_JobINFO gives you a quick overview of how your SQL Server Agent jobs are configured. This in...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 20:00:09 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=10 SQL_IndexUSAGE: This procedure shows whether your indexes on tables are useful or not. This script ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 19:56:11 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=9 SQL_HighCPU script helps you detect the highest consuming CPU queries. Very useful for troublesho...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 19:50:35 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=8 SQL_DeFraGGer25 allows you to defrag indexes from a single database to all the databases. This r...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 19:04:04 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=7 This script allows you to obtain information related to files/file group associated with your SQL ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 18:58:04 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=6 SQL_DBMStatus provides you with a quick status on your database mirroring session. You MUST alwa...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 18:55:36 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=5 SQL_DBMFailOVER allows you to perform database failover from the principal server instance to the...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 18:53:20 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=4 SQL_DataGROWTH tool allows you to capture daily data growth from every single object of your SQL Se...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 08:04:05 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=3 SQL_CheckDBALL can be used to check the allocation and structural integrity of all the objects con...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 06:04:45 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=2This script can be used to generate detach and attach script for one or all the databases from your ...http://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspxSun, 26 Aug 2007 05:55:56 GMThttp://www.sqlcommunity.com/ScriptsTools/SQLServerScriptsCategorized/tabid/56/language/en-US/Default.aspx?id=1