jujiro

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

jujiro

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

jujiro

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 

jujiro

-- There are two examples on this page
-- 1st Example
begin
    DECLARE @idoc INT
    DECLARE @xml nvarchar(MAX)
    SET @xml='<root>
<id>
19
</id>
<prod>

jujiro

public class TypeMapping
    {
        public Dictionary<string, string> SqlToCSharpMappings = new Dictionary<string, string>()
        {
            {"BINARY","byte[]"},
            {"BIGINT","long?"},
            {"BIT","bool?"},
            {"CHAR","string"},
            {"DATETIME2","DateTime?"},
            {"DECIMAL","decimal?"},

jujiro

public static Type GetClrType(SqlDbType sqlType)
{
    switch (sqlType)
    {
        case SqlDbType.BigInt:
            return typeof(long?);
 
        case SqlDbType.Binary:
        case SqlDbType.Image:
        case SqlDbType.Timestamp:

jujiro

/*
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

jujiro

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