A quick way to generate script to disable all FK constraints in current db:

SELECT ‘ALTER TABLE ‘ + B.name + ‘ NOCHECK CONSTRAINT ‘ + C.name
FROM sysconstraints A
, sysobjects B
, sysobjects C
WHERE A.id = B.id
AND A.constid = C.id
AND C.type = ‘F’

A quick way to generate script to enable all FK constraints in current db:

SELECT ‘ALTER TABLE ‘ + B.name + ‘ CHECK CONSTRAINT ‘ + C.name
FROM sysconstraints A
, sysobjects B
, sysobjects C
WHERE A.id = B.id
AND A.constid = C.id
AND C.type = ‘F’

This sql generates a grant blanket execute on stored procedures in a database:

GRANT EXECUTE TO {USER_NAME}
GO

This sql generates a grant execute statement for each stored procedure in the database:

SELECT ‘GRANT EXECUTE ON ‘ + name + ‘ TO {USER_NAME}’
FROM sysobjects
WHERE type IN ( ‘P’, ‘FN’ )
AND name NOT LIKE ‘dt_%’
ORDER BY name

This scripts grants execute to all stored procedures in the database:

USE {DATABASE_NAME}
GO

DECLARE @proc_name varchar(128)
DECLARE @sql nvarchar(255)
DECLARE proc_cur CURSOR FAST_FORWARD
FOR
SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE type IN (‘P’,’FN’)
OPEN proc_cur
FETCH proc_cur INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘grant execute on ‘ + @proc_name + ‘ to {USER_NAME}’
EXECUTE sp_executesql @sql
FETCH proc_cur INTO @proc_name
END
CLOSE proc_cur
DEALLOCATE proc_cur
GO

To create the linked server:

USE [msdb]
GO

EXEC sp_addlinkedserver @server = N’SAMPLE_LINK’, @srvproduct=N’Oracle’, @provider=N’OraOLEDB.Oracle’, @datasrc=N’LINKDSN’
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘SAMPLE_LINK’, @rmtuser = ‘LINKUSER’, @rmtpassword = ‘*******’, @useself = False
GO

To drop the linked server:

USE [msdb]
GO

EXEC sp_droplinkedsrvlogin ‘SAMPLE_LINK’, NULL
GO

EXEC sp_dropserver ‘SAMPLE_LINK’
GO

CREATE FUNCTION fn_Split

( @text varchar(8000)

, @delimiter varchar(20) = ' '

)
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY
, value varchar(8000)   
)
AS
BEGIN

DECLARE @index int 

SET @index = -1 

WHILE (LEN(@text) > 0) 
 BEGIN  
   SET @index = CHARINDEX(@delimiter , @text)  
   IF (@index = 0) AND (LEN(@text) > 0)  
    BEGIN   
     INSERT INTO @Strings VALUES (@text)
         BREAK  
    END  
   IF (@index > 1)  
    BEGIN   
      INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
      SET @text = RIGHT(@text, (LEN(@text) - @index))  
    END  
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index)) 
 END
RETURN
END
GO

USE [dbname]
GO

—————————————-
— Drop all constraints (PKs & FKs)
—————————————-
SET NOCOUNT ON
DECLARE @id int
, @objname nvarchar(256)
, @constr_name nvarchar(128)
, @count int
, @sql nvarchar(2000)
DECLARE @objs AS TABLE ( id int identity(1,1), objname nvarchar(256), objtype varchar(128))
INSERT INTO @objs ( objname, objtype )
SELECT table_schema + ‘.’ + table_name
, constraint_name
FROM information_schema.table_constraints
ORDER BY table_name
, constraint_type
SELECT @id = max(id) FROM @objs
SET @count = @id
WHILE @id > 0
BEGIN
SELECT @objname = objname
, @constr_name = objtype
FROM @objs WHERE id = @id
SET @sql = ‘ALTER TABLE ‘ + @objname + ‘ DROP CONSTRAINT [‘ + @constr_name + ‘]’
PRINT @sql
–EXECUTE sp_executesql @sql
SET @id = @id – 1
SET @count = @count + 1
END
PRINT CAST(@count as varchar(10)) + ‘ Constraint(s) deleted’
GO

—————————————-
— Drop all Indexes
—————————————-
SET NOCOUNT ON
DECLARE @id int
, @tabname nvarchar(128)
, @idxname nvarchar(128)
, @ispk tinyint
, @count int
, @sql nvarchar(2000)
DECLARE @objs AS TABLE ( id int identity(1,1), tabname nvarchar(128), idxname varchar(128), ispk tinyint )
INSERT INTO @objs ( tabname, idxname, ispk )
SELECT OBJECT_NAME(si.object_id) “tabname”
, si.name “idxname”
, si.Is_Primary_Key “ispk”
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc
ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, ‘IsUserTable’) = 1
AND si.name IS NOT NULL
ORDER BY 1, 2
SELECT @id = max(id) FROM @objs
WHILE @id > 0
BEGIN
SELECT @tabname = tabname
, @idxname = idxname
, @ispk = ispk
FROM @objs
WHERE id = @id
SET @sql = CASE @ispk when 1 then ‘ALTER TABLE [‘ + @tabname + ‘] DROP CONSTRAINT [‘ + @idxname + ‘]’
else ‘DROP INDEX [‘ + @idxname + ‘] ON [‘ + @tabname + ‘] WITH ( ONLINE = OFF )’ END
PRINT @sql
SET @id = @id – 1
END
GO

use sandbox1
go

— Script out indexes completely, including both PK’s and regular indexes, each clustered or nonclustered.
— DOES NOT HANDLE COMPRESSION; that’s ok, since 2008 R2 RTM benchmarking shows it’s faster and results in smaller indexes to insert uncompressed and then compress later
— HARDCODES [dbo] schema (i.e. it doesn’t say [JohnDoe].[table], changing that to [dbo].[table]
— modified from original version from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(MAX),
@sIndexCols VARCHAR(MAX),
@sSQL VARCHAR(MAX),
@sParamSQL VARCHAR(MAX),
@sFilterSQL VARCHAR(MAX),
@location SYSNAME,
@IndexCount INT,
@CurrentIndex INT,
@CurrentCol INT,
@Name VARCHAR(128),
@IsPrimaryKey TINYINT,
@Fillfactor INT,
@FilterDefinition NVARCHAR(MAX),
@IsClustered BIT, — used solely for putting information into the result table
@PKDropSQL NVARCHAR(MAX)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]’))
DROP TABLE [dbo].[#IndexSQL]

CREATE TABLE #IndexSQL
( TableName VARCHAR(128) NOT NULL
,IndexName VARCHAR(128) NOT NULL
,IsClustered BIT NOT NULL
,IsPrimaryKey BIT NOT NULL
,IndexCreateSQL NVARCHAR(max) NOT NULL
,PKDropSQL NVARCHAR(max) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]’))
DROP TABLE [dbo].[#IndexListing]

CREATE TABLE #IndexListing
(
[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID] INT NOT NULL,
[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID] INT NOT NULL,
[IsPrimaryKey] TINYINT NOT NULL,
[FillFactor] INT,
[FilterDefinition] NVARCHAR(MAX) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]’))
DROP TABLE [dbo].[#ColumnListing]

CREATE TABLE #ColumnListing
(
[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable] INT NOT NULL,
[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex] INT NOT NULL,
[IsIncludedColumn] BIT NULL
)

INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL –si.filter_definition –filter_definition is in sql 2008
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, ‘IsUserTable’) = 1
ORDER BY OBJECT_NAME(si.object_id), si.index_id

SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

WHILE @CurrentIndex 0
BEGIN

SELECT @CurrentCol = 1

SELECT @IxFirstColumn = 1, @sIncludeCols = ”, @sIndexCols = ”

WHILE @CurrentCol 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ‘,’
END

SET @sIncludeCols = @sIncludeCols + ‘[‘ + @IxColumn + ‘]’

END

SET @CurrentCol = @CurrentCol + 1
END

TRUNCATE TABLE #ColumnListing

–append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ‘) ‘ + ‘ INCLUDE ( ‘ + @sIncludeCols + ‘ ) ‘
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ‘) ‘

— Add filtering
IF @FilterDefinition IS NOT NULL
SET @sFilterSQL = ‘ WHERE ‘ + @FilterDefinition + ‘ ‘ + CHAR(13)
ELSE
SET @sFilterSQL = ”

— Build the options
SET @sParamSQL = ‘WITH ( PAD_INDEX = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsPadIndex’) = 1
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ ALLOW_PAGE_LOCKS = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsPageLockDisallowed’) = 0
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ ALLOW_ROW_LOCKS = ‘

IF INDEXPROPERTY(@idxTableID, @idxname, ‘IsRowLockDisallowed’) = 0
SET @sParamSQL = @sParamSQL + ‘ON,’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF,’

SET @sParamSQL = @sParamSQL + ‘ STATISTICS_NORECOMPUTE = ‘

— THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
IF (INDEXPROPERTY(@idxTableID, @idxname, ‘IsStatistics’) = 1)
SET @sParamSQL = @sParamSQL + ‘ON’
ELSE
SET @sParamSQL = @sParamSQL + ‘OFF’

— Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
IF ISNULL( @FillFactor, 90 ) 0
SET @sParamSQL = @sParamSQL + ‘ ,FILLFACTOR = ‘ + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )

IF (@IsPrimaryKey = 1) — DROP_EXISTING isn’t valid for PK’s
BEGIN
SET @sParamSQL = @sParamSQL + ‘ ) ‘
SET @PKDropSQL = ‘ALTER TABLE [‘ + @idxTableName + ‘] DROP CONSTRAINT [‘ + @idxName + ‘]’
END
ELSE
BEGIN
SET @sParamSQL = @sParamSQL + ‘ ,DROP_EXISTING = ON ) ‘
SET @PKDropSQL = NULL
END

SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL

— 2008 R2 allows ON [filegroup] for primary keys as well, negating the old “IF THE INDEX IS NOT A PRIMARY KEY – ADD THIS – ELSE DO NOT” IsPrimaryKey IF statement
SET @sSQL = @sSQL + ‘ ON [‘ + @location + ‘]’

–PRINT @sIndexCols + CHAR(13)
INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, PKDropSQL)
VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @PKDropSQL)

END

SET @CurrentIndex = @CurrentIndex + 1
END

SELECT * FROM #IndexSQL

This is a replacement for the undocumented sp_MSforeachdb system stored proc.

USE [master];
GO

CREATE PROCEDURE dbo.sp_foreachdb
@command NVARCHAR(MAX),
@replace_character NCHAR(1) = N’?’,
@print_dbname BIT = 0,
@print_command_only BIT = 0,
@suppress_quotename BIT = 0,
@system_only BIT = NULL,
@user_only BIT = NULL,
@name_pattern NVARCHAR(300) = N’%’,
@database_list NVARCHAR(MAX) = NULL,
@recovery_model_desc NVARCHAR(120) = NULL,
@compatibility_level TINYINT = NULL,
@state_desc NVARCHAR(120) = N’ONLINE’,
@is_read_only BIT = 0,
@is_auto_close_on BIT = NULL,
@is_auto_shrink_on BIT = NULL,
@is_broker_enabled BIT = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db NVARCHAR(300),
@i INT;

IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n

use master
go

CREATE PROCEDURE sp_GetAllTableSizes
AS
DECLARE @tabname varchar(128)

–Cursor to get the name of all user tables from the sysobjects listing
DECLARE table_cur CURSOR FAST_FORWARD FOR
SELECT [name]
FROM dbo.sysobjects (NOLOCK)
WHERE OBJECTPROPERTY(id, N’IsUserTable’) = 1

–A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(128),
numberofRows int,
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

–Open the cursor
OPEN table_cur

–Get the first table name from the cursor
FETCH NEXT FROM table_cur INTO @tabname

–Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
–Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @tabname

–Get the next table name
FETCH NEXT FROM table_cur INTO @tabname
END

–Get rid of the cursor
CLOSE table_cur
DEALLOCATE table_cur

–Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY numberofRows DESC

–Final cleanup!
DROP TABLE #TempTable

GO