Default New Query for SQL Server Management Studio

I found an article about how to change this on the web.  The file can be found at C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql for SQL 2008.  It can be found at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql for SQL 2005.  I thought I would give it a shot and see what became useful for me. This is now my default New Query document contents in both SSMS 2005 and SSMS 2008.

-- Set the transaction isolation level explictly
SET TRANSACTION ISOLATION LEVEL READ uncommitted

BEGIN TRAN

BEGIN TRY

-- Replace this with the actual work ;)

-- Here is an example breakpoint
RAISERROR (N'BREAKPOINT.',16,1);
END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT Error_number() AS errornumber,
Error_severity() AS errorseverity,
Error_state() AS errorstate,
Error_procedure() AS errorprocedure,
Error_line() AS errorline,
Error_message() AS errormessage;

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

RESEED all tables with Identity Columns in a Database

I had a problem today where a comparison tool had inserted lots of data into a database and it was then propagated to other environments. We started seeing errors and needed to ensure that all tables were fixed. This was the hack I came up with. Enjoy

DECLARE @SQL NVARCHAR(MAX)

DECLARE reseed CURSOR FAST_FORWARD FOR
SELECT N'DBCC CHECKIDENT (' + Quotename(Object_name(object_id)) + N',RESEED)'
FROM sys.columns
WHERE is_identity = 1
AND Objectproperty(object_id,'IsTable') = 1

OPEN reseed

FETCH NEXT FROM reseed
INTO @SQL

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
EXEC dbo.Sp_executesql
@SQL
END

FETCH NEXT FROM reseed
INTO @SQL
END

CLOSE reseed
DEALLOCATE reseed

REPOST How to see when restores happend and what files were restored

How to identify when a database was restored, the source of the backup and the date of the backup

Repost: List of tables in dependency order

A great script article on getting a list of tables in foreign key dependency order.

http://sqlblog.com/blogs/jamie_thomson/archive/2009/09/08/deriving-a-list-of-tables-in-dependency-order.aspx

with fk_tables as (
select s1.name as
from_schema
, o1.Name as
from_table
, s2.name as
to_schema
, o2.Name as
to_table
from sys.foreign_keys fk
inner join
sys.objects
o1
on fk.parent_object_id = o1.object_id
inner join
sys.schemas
s1
on o1.schema_id
= s1.schema_id
inner join
sys.objects
o2
on fk.referenced_object_id = o2.object_id
inner join
sys.schemas
s2
on o2.schema_id
= s2.schema_id
/*For the purposes of finding dependency
hierarchy
we're not
worried about self-referencing tables*/
where not ( s1.name
= s2.name
and o1.name =
o2.name)
)
,ordered_tables AS
( SELECT s.name as
schemaName
, t.name as
tableName
, 0 AS Level
FROM ( select *
from sys.tables
where name <> 'sysdiagrams')
t
INNER JOIN sys.schemas
s
on t.schema_id
= s.schema_id
LEFT OUTER
JOIN fk_tables fk
ON s.name =
fk.from_schema
AND t.name =
fk.from_table
WHERE fk.from_schema IS NULL
UNION ALL
SELECT fk.from_schema
, fk.from_table
, ot.Level + 1
FROM fk_tables fk
INNER JOIN
ordered_tables ot
ON fk.to_schema =
ot.schemaName
AND fk.to_table =
ot.tableName
)select distinct ot.schemaName
, ot.tableName
, ot.Level
from ordered_tables ot
inner join (
select schemaName
, tableName
, MAX(Level)
maxLevel
from ordered_tables
group by
schemaName,tableName
) mx
on ot.schemaName =
mx.schemaName
and ot.tableName =
mx.tableName
and mx.maxLevel =
ot.Level;