Skip to content

Instantly share code, notes, and snippets.

Ashish Kumar jujiro

View GitHub Profile
@jujiro
jujiro / run-single-instance-stored-proc.sql
Created May 20, 2019
Running only one instance of Sql Server stored procedure
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)
)
@jujiro
jujiro / initialize-column-in-large-table.sql
Created May 20, 2019
Initializing a newly added column in a very large table
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.
@jujiro
jujiro / find-identity-columns.sql
Created May 20, 2019
Find identity columns (Sql server)
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
@jujiro
jujiro / pass-xml-instad-of-table-to-c-sharp.sql
Last active May 20, 2019
XML is easier to pass to a sql stored procedure than a table in .Net. Refactor your proc and you will save a ton of code in .Net.
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>
@jujiro
jujiro / sql-to-clr-type-basic.cs
Created May 20, 2019
Sql data type mappings to some basic clr types
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?"},
@jujiro
jujiro / sql-to-c-sharp.cs
Last active May 20, 2019
Sql to C# data type conversion code snippet
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:
@jujiro
jujiro / find-default-constraint-names.sql
Created May 20, 2019
Find default constraint names for tables (Sql Server)
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
@jujiro
jujiro / find-table-dependencies.sql
Created May 20, 2019
Find table dependencies (Sql Server)
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
You can’t perform that action at this time.