TSQL : Generating GUIDs

Non-sequential, but UNIQUE ID values >

SELECT NewID()

8E8DDFD9-568C-4815-832B-551604AEFB9F

sp_changeobjectowner

Changes the owner of an object (table/view/sp etc) >

use :
sp_changeobjectowner [objectname] , [new owner]

example :

sp_changeobjectowner 'fasttrackuser.FastTrackPackageDocumentsCombinations', 'dbo'

remarks :
must have appropriate permissions to perform this action.

USP_DropTableConstraints (SQL 2000 Version)

Drops Constraints for given table name (SQL 2000 Version)>

CREATE PROCEDURE USP_DropTableConstraints @tablename VARCHAR(128)
         
AS

 SET NOCOUNT ON

 DECLARE  @constraintname VARCHAR(128),
          @sqlcmd         VARCHAR(1024)
                    
 DECLARE CONSTRAINTSCURSOR CURSOR  FOR
 SELECT NAME
 FROM   SYSOBJECTS
 WHERE  XTYPE IN ('C','F','PK','UQ',
                  'D')
        AND (STATUS & 64) = 0
        AND PARENT_OBJ = OBJECT_ID(@tablename)
                        
 -- nb : xtype refers to CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints.

 OPEN CONSTRAINTSCURSOR

 FETCH NEXT FROM CONSTRAINTSCURSOR
 INTO @constraintname

 WHILE (@@FETCH_STATUS = 0)
   BEGIN
     SELECT @sqlcmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constraintname
     EXEC( @sqlcmd)
     FETCH NEXT FROM CONSTRAINTSCURSOR
     INTO @constraintname
   END
  
 CLOSE CONSTRAINTSCURSOR

 DEALLOCATE CONSTRAINTSCURSOR

 RETURN 0
       
GO

Good Practice - Schema Scripting

Dealing with multiple situtations involving lazy developers and offshored software houses means i am supplied a fair number of (often untested) scripts.

Here are some examples for those supplying schema modifications >

example 1 - When replacing an database object, check for its existance
and drop it first before attempting to create it.

IF EXISTS(select * from information_schema.tables
    where table_name ='FORECAST_BREAKDOWN')
BEGIN
 DROP TABLE [dbo].[FORECAST_BREAKDOWN]
END

 CREATE TABLE [dbo].[FORECAST_BREAKDOWN](
  [STACK_ID] [numeric](10, 0) NOT NULL,
  [PLOT_CODE] [varchar](20) NOT NULL,
  [FB_AMOUNT] [numeric](35, 10) NOT NULL CONSTRAINT [DF__FORECAST___FB_AM__54EB90A0]  DEFAULT ((0)),
  [DESCRIPTION] [varchar](255) NULL,
  [FB_DATE] [datetime] NULL)
go

example 2 - Check for the existence of a column before attempting to add it.

IF NOT EXISTS(select * from information_schema.columns
    where table_name ='forecast_breakdown'
    and column_name = 'srno')
BEGIN
 PRINT 'Column not present, creating...'
 ALTER TABLE [FORECAST_BREAKDOWN] ADD SRNO INT
END
ELSE
BEGIN
 PRINT 'Column already present'
END

These examples show how to script table and column changes. There are many more INFORMATION_SCHEMA views documented here > http://msdn.microsoft.com/en-us/library/ms186778.aspx

Restore TSQL

RESTORE DATABASE [databasename]
FROM DISK = N'c:\database.bak'
WITH MOVE 'databasename' TO 'D:\data\databasename.mdf',
MOVE 'databasename_log' TO 'E:\logs\databasename.ldf', NOUNLOAD, STATS = 10
GO

SQL : Data Search Script

/*
dynamic sql to search for data
*/

select  'if exists (select 1 from [' +
 table_name +
 '] where ' +
 column_name +
 ' like ''%Fred Bloggs%'' ) print ''' +
 table_name +
 '/' +
 column_name +
 '''' +
 char(10) +
 'go'
from  information_schema.columns
where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar')
and table_schema = 'dbo'
and column_name <> 'order'

Stored procedure permissions script

select 'Grant EXEC on ' + name + ' to MyUser' 
  from sysobjects 
 where type = 'P' 

Full Text Indexes - Indexing Priority

To set the priority for full text indexes i.e. how much processor is dedicated to performing the indexing process (and consequently how quickly the job completes), use the following command.

sp_fulltext_service 'resource_usage' , n

where n is a value from 1 to 5 representing low to high priority respectively.