SQL Server Management Studio Hack

Database administrators are lazy by nature. And although Books Online is a very good resource I”d like to get my own mini overview of most used statements in a new query template such as this one:

 

So whenever I hit “New Query” I get my template. It”s also usefull when you want to send your script to a colleague so you won”t have to tell him/her what server they need to connect to and which database to select.

You might want to give it a try.

1. Open file ”C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDESqlWorkbenchProjectItemsSQLSQLFile.sql” in Management Studio and copy the code below and save.

– ! Please execute in SQLCMD mode ! click SQLCMD Mode on the SQL Server Management Studio Query menu,

SET NOCOUNT ON
:CONNECT <servername>

USE <databasename>
GO

 
/*

 DBCC SQLPERF(LOGSPACE)WITH NO_INFOMSGS
 DBCC OPENTRAN WITH TABLERESULTS
 DBCC INPUTBUFFER ( session_id )
 DBCC SHOW_STATISTICS (“Person.Address”, AK_Address_rowguid) WITH HISTOGRAM;
 DBCC SHOWCONTIG (“HumanResources.Employee”);
 DBCC FREEPROCCACHE WITH NO_INFOMSGS;
 DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]
 DBCC CHECKCONSTRAINTS (“Production.CK_ProductCostHistory_EndDate”);
 DBCC DBREINDEX (“HumanResources.Employee”, ” “, 70);
 DBCC INDEXDEFRAG (AdventureWorks, “Production.Product”, PK_Product_ProductID)
 DBCC CHECKDB;
 DBCC CHECKTABLE (“HumanResources.Employee”) WITH PHYSICAL_ONLY;

 EXEC sp_helpfile
 EXEC sp_spaceused
 EXEC sp_change_users_login ”Report”;
 EXEC master.dbo.xp_fixeddrives

*/
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>