Add trigger to all tables in schema

The code below will add a trigger to all the tables in a specific schema on the Microsoft
SQL-server.

 

Getting accessible schemas for a user

The code below gets the schemas from a Microsoft SQL-server that is accessible for a given user. It is made with SAS-code but the SQL-statement can be used regardless of using SAS.

Bulk delete in MS-SQL server

If you want to simulate bulk-deleting in Microsoft SQL-server this can do the trick. I use it to avoid getting a transaction log that fills up the harddisk.
The example uses a stored procedure, but you don’t have to do this. The stored procedure is given a year-variable that is used in the SQL-sentences that selects what has to be deleted.

USE [YOUR_DATABASE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_BULK_DELETE_AREA_YEAR @ActionYear char(4)
AS
BEGIN

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.

SET NOCOUNT ON;

— Insert statements for procedure here
DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT

SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
COMMIT TRAN
END
END
GO

It’s excecuted in the following manner: exec sp_BULK_DELETE_AREA_YEAR ‘2011’

I haven’t figured out how to use a variable that contains the tablename. So I don’t think it’s possible. You have to write the table name.
The solution was found here.

Enable xp_cmdshell in Microsoft SQL-server

Disclaimer: Beware of the possible security threat when you enable xp_cmdshell usage. You can also take a look at this article, it’s at bit more complicated, but a lot safer.
The article pretty much walks you through, what you need to do to use SQLCLR. I tried it out and got stuck when i got a security threat. I got the threat because you have to give the assemblies that you use (‘alter assemblies…’) a given state – safe, unsafe and external in the SQL-server.

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

It’s also possible to use ‘sp_oacreate’.

Partitioning in Microsoft SQL-server 2008

When partitioning tables in Microsoft SQL-server 2008 you could get this error when your trying to move to the next step after mapping your partitions.

The number of filegroups or range values is not valid. Enter an extra filegroup in addition to the number of boundary values.

Your partition mapping will probably look something like this.

The error occurs because you haven’t choosen a filegroup for the last empty partition (the one after 2011). Your partition mapping should look like this.

In SQL the partition function will look like this.

CREATE PARTITION FUNCTION [<pf_Year>](varchar(4))
AS RANGE RIGHT
FOR VALUES (N’2007′, N’2008′, N’2009′, N’2010′, N’2011′)

Tablesample

This SQL-sentence will first of all create a table (destinationtable) containing variable1, variable2 and variable3. Into the destinationtable it will make a tablesample from 10 percent of the observations in the soucetable.

SELECT [variable1]
,[variable2]
,[variable3]
into [databse].[schema].[destinationtable]
from [database].[schema].[sourcetable]
tablesample (10 percent)

You could eg. use tempdb (a temporary database in Microsoft SQL-server) to store the sample.