Grant execute permissions to all stored procedures to a specific user
CREATE PROC grantexecutepermission(
@UserName NVARCHAR(250))
AS
DECLARE curse CURSOR FOR
SELECT name
FROM sysobjects
WHERE TYPE = 'P'
OPEN curse
DECLARE @proc VARCHAR(100)
DECLARE @stmt NVARCHAR(200)
FETCH NEXT FROM curse
INTO @proc
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt = 'grant execute on ' + @proc + ' to ' + @UserName
EXEC sp_executesql
@STMT
PRINT @stmt
FETCH NEXT FROM curse
INTO @proc
END
CLOSE curse
DEALLOCATE curse
Check if column exists before adding
IF NOT EXISTS (select * from Information_SCHEMA.columns
WHERE Table_name='ExampleTable' and column_name='ExampleColumn')
BEGIN
ALTER TABLE ExampleTable
ADD ExampleColumn nvarchar(350)
END
GO
Use a cursor to print out column values to a pre-formatted string
SET NOCOUNT ON
DECLARE @BTDocumentId int
DECLARE @BTDocumentVersionNo int
DECLARE myCursor CURsOR FOR
SELECT Id as BTDocumentId, VersionNo as BTDocumentVersionNo from document where istemplate = 1
OPEN myCursor
FETCH NEXT FROM myCursor INTO @BTDocumentId, @BTdocumentVersionNo
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'BTVersionId=' + CAST(@BTDocumentId AS VARCHAR(500)) + ' AND BTDocumentVersionNo=' + CAST(@BTDocumentVersionNo AS VARCHAR(500)) + ', '
FETCH NEXT FROM myCursor INTO @BTDocumentId, @BTdocumentVersionNo
END
CLOSE myCursor
DEALLOCATE myCursor
Reseed a table
DBCC CHECKIDENT ("Risk", RESEED, 920617);
Drop column – remove foreign key reference first
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='dbo' AND CONSTRAINT_NAME='FK_Coverage_CobCode' AND TABLE_NAME='Coverage')
BEGIN
ALTER TABLE dbo.Coverage DROP CONSTRAINT FK_Coverage_CobCode;
END
GO
IF EXISTS (select * from Information_SCHEMA.columns
WHERE Table_name='Coverage' and column_name='CobCodeId')
BEGIN
ALTER TABLE Coverage
DROP COLUMN CobCodeId
END
GO