Skip to content

Instantly share code, notes, and snippets.

Ashish Kumar jujiro

Block or report user

Report or block jujiro

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

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