Scripts « Lohn Solutions

The easy way to find deadlocks

A quick, simple and “easy” way to find deadlocks I’ve found is to do the following:

Turn on the trace flag 1222
Wait for a deadlock to happen
Open query analyzer and run the following script:

Create Table #ErrorLogTable
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
EXEC xp_readerrorlog
Select
*
– , Left(right(loginfo, Len(loginfo) – 20), charindex(' ',(right(loginfo, Len(loginfo) – 20))))
from [...]

SQL 2008 System Generated Names for Defaults

I had to create a script for dropping constraints on a table – no problem if the constaint is a named default, but if the default has a system generated name, it gets a bit hairy. Here’s an example of how the defaults get created:

Create table TestTable1
(TestConst datetime default Getdate())
Go

I googled this and found Pinal Dave had [...]

Get Fairly Accurate Table Counts

Here’s a simple script that I use often – a quick hitter to find table row counts, that doesn’t use the Select Count(*). It uses the sysindexes table thus the reason it’s called “fairly accurate”.

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER [...]

Script for Running Events

Here’s a script I created to determine when I was going to be at specific miles for a marathon. It’ll give you your miles per hour and your minutes per mile. Then it’ll calculate at what time you will be at different mile marks.

Set nocount on
Declare @FTime datetime,
@Dist float,
@RStartTime datetime,
@BDown float

———————————–
—-Change these for [...]

Script to Create a Script Header….

/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
– Creates a header for scripts –
– Pastes [...]

Table Size

–This will give you the amount of disk space that each table uses in a database.

SET NOCOUNT ON
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit

CREATE TABLE #TempTable
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)

SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
INSERT INTO #TempTable EXEC(@cmdstr)

–Use one of these depending what you are looking for.
–SELECT * FROM #TempTable ORDER BY Table_Name
SELECT * FROM [...]

Script to display all date formats

I’m writing this in response to Adam Machanic’s – T-SQL Tuesday - #001: Date/Time Tricks.

Here’s the a very useful script I still use all the time. I created it when I was working on a reporting project – it simply displays all the different date formats in SQL server.

–If you are looking to display dates, [...]

SQL 2005 – Script to find missing indexes

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT weighted_cost = avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),
s.*,
'CREATE INDEX IX_' +
REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), '[', ''), ']', '') +
' ON ' + d.statement + '(' + COALESCE(equality_columns, inequality_columns) +
CASE WHEN equality_columns IS NOT NULL
  THEN
  CASE WHEN inequality_columns IS NOT NULL
   THEN ', ' + inequality_columns
   END
  END       + ')' +
  CASE WHEN included_columns IS NOT NULL
   THEN ' INCLUDE [...]

Lohn Solutions

Categories

Archives

Blogroll