View run-single-instance-stored-proc.sql
if not exists (select * from dbo.sysobjects | |
where id = object_id(N'dbo.p_AcquireLock') and | |
OBJECTPROPERTY(id, N'IsProcedure') = 1) | |
exec('create procedure dbo.p_AcquireLock as select 1') | |
go | |
alter procedure dbo.p_AcquireLock | |
( | |
@LockName nvarchar(255) | |
) |
View initialize-column-in-large-table.sql
-- Adding a new column, which you want to initialize with a default value (often true with bit columns) | |
-- can be tricky. Our gut feeling is to write a quick update statement, or use a construct like the following. | |
ALTER TABLE dbo.a_very_large_table | |
ADD test INT NOT NULL DEFAULT(0) | |
GO | |
/* | |
Both these methods should be avoided at all cost. They create a very large transaction, creating a situation of blocking or getting blocked. If blocked, rollback process can take hours. | |
The following script proposes two methods. | |
First one uses a controlled update using row count and the second one uses a cursor. |
View find-identity-columns.sql
select | |
i.table_catalog, | |
i.table_schema, | |
i.table_name, | |
c.name as column_name | |
from | |
( | |
sys.objects o inner join | |
sys.columns c | |
on |
View pass-xml-instad-of-table-to-c-sharp.sql
-- There are two examples on this page | |
-- 1st Example | |
begin | |
DECLARE @idoc INT | |
DECLARE @xml nvarchar(MAX) | |
SET @xml='<root> | |
<id> | |
19 | |
</id> | |
<prod> |
View sql-to-clr-type-basic.cs
public class TypeMapping | |
{ | |
public Dictionary<string, string> SqlToCSharpMappings = new Dictionary<string, string>() | |
{ | |
{"BINARY","byte[]"}, | |
{"BIGINT","long?"}, | |
{"BIT","bool?"}, | |
{"CHAR","string"}, | |
{"DATETIME2","DateTime?"}, | |
{"DECIMAL","decimal?"}, |
View sql-to-c-sharp.cs
public static Type GetClrType(SqlDbType sqlType) | |
{ | |
switch (sqlType) | |
{ | |
case SqlDbType.BigInt: | |
return typeof(long?); | |
case SqlDbType.Binary: | |
case SqlDbType.Image: | |
case SqlDbType.Timestamp: |
View find-default-constraint-names.sql
/* | |
Sql server will not let you dropa table if it has default contraints (like default values for columns.) | |
This script will let you find them. | |
1. Determine the constraint name. | |
*/ | |
if object_id('[dbo].[GetDefaultConstraintName]') is not null | |
drop function [dbo].[GetDefaultConstraintName] | |
go |
View find-table-dependencies.sql
begin | |
-- Use this script to find the dependencies of tables. | |
-- Set the following two variables. | |
-- #################################################### | |
declare @TableSchema varchar(255)='Production' | |
declare @TableName varchar(255)='Product' | |
-- #################################################### | |
declare @nCnt1 int |