Ashish Kumar jujiro

## 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)
)

## 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.

## 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

## 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>

## 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?"},

## 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:

## 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

## 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