Reindex All Tables in a Database
-
Posted on June 17, 2009 by Derek Dieter
Code for reindexing all tables in a database using dbreindex
Continue reading ...Dynamically Drop Table Constraints
-
Posted on June 17, 2009 by Derek Dieter
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 ...Disable All SQL Server Jobs
-
Posted on May 31, 2009 by Derek Dieter
[cc lang=”sql”] CREATE TABLE #Job_Names ( Job_Name SYSNAME NOT NULL ) INSERT INTO #Job_Names SELECT name FROM msdb.dbo.sysjobs ORDER BY name DECLARE @job_name SYSNAME DECLARE @job_id UNIQUEIDENTIFIER DECLARE disable_jobs CURSOR FOR SELECT Job_Name FROM #Job_Names SET @job_id = NULL OPEN disable_jobs FETCH NEXT FROM disable_jobs INTO @job_name WHILE @@FETCH_STATUS = 0 BEGIN EXEC msdb.dbo.sp_verify_job_identifiers ‘@job_name’, ‘@job_id’, @job_name OUTPUT, @job_id OUTPUT EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0 SET @job_id = NULL FETCH NEXT FROM disable_jobs INTO @job_name END CLOSE disable_jobs DEALLOCATE disable_jobs DROP TABLE #Job_Names [/cc]
Continue reading ...
