Turn On Deadlock Trace Flag

DBCC TRACEON (1204, -1) Continue reading ...

Create Date Table

create table dates( id int not null, [date] as (yyyy+mm+dd), yyyy char(4) not null, mm char(2) not null,

Find Missing Table and Index Statistics

SELECT Last_Updated = STATS_DATE(si.id, si.indid) ,TableName = object_name(si.id) ,Name = RTRIM(si.name) ,Size = DATALENGTH (si.statblob) FROM sysindexes si WITH (nolock) WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1 --AND INDEXPROPERTY (si.id , si.name , 'IsAutoStatistics' ) = 0 order by last_updated, tablename Continue reading ...

Reindex All Tables in a Database

Code for reindexing all tables in a database using dbreindex

Dynamically Drop Table Constraints

System generated constraints take on a naming convention of their own. Unfortunately the naming convention in production is rarely the same name in the uncontrolled environments. Using this script, you can dynamically drop all system generated constraints. It doesn’t go as far are re-creating them, however it’s a start. Just change the values of the @TableSchema and TableName variables below: [cc lang=”sql”] DECLARE @TableName varchar(100) DECLARE @TableSchema varchar(100) DECLARE @CountConst int DECLARE @default sysname DECLARE @SQLDropMe varchar(max) DECLARE @ColumnNames varchar(max) SET @TableSchema = ‘dbo’ SET @TableName = ’employees’ ——————————————— Store Existing Column Names SET @ColumnNames = SUBSTRING((SELECT ‘,’ + r.COLUMN_NAME FROM( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @TableSchema AND TABLE_NAME = @TableName ) r FOR XML PATH(”) ), 2, 8000) ——————————————— /Store Existing Column Names ——————————————— Insert contents into temp table EXEC (‘SELECT * INTO ‘ + @TableSchema + ‘.tmp01_’ + @TableName + ‘ FROM ‘ + @TableSchema + ‘.’ + @TableName) ——————————————— /Insert contents into temp table ——————————————— Drop all the constraints DECLARE @TableConstraints TABLE ( ID int IDENTITY(1,1) ,DefaultConst sysname ) INSERT INTO @TableConstraints ( DefaultConst ) SELECT object_name(default_object_id) FROM sys.columns WHERE object_id = object_id(@TableSchema + ‘.’ + @TableName) AND object_name(default_object_id) IS NOT NULL SET @CountConst = (SELECT MAX(ID) FROM @TableConstraints) WHILE @CountConst > 0 BEGIN SET @Default = (SELECT DefaultConst FROM @TableConstraints WHERE ID = @CountConst) SET @SQLDropMe = ‘ALTER TABLE ‘ + @TableSchema + ‘.’ + @TableName + ‘ DROP CONSTRAINT ‘ + @default SELECT @SQLDropMe –EXEC (@SQLDropMe) SET @CountConst = @CountConst – 1 END […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!