Find all identity columns in SQL server

select
table_schema
,table_name
, column_name
, data_type
, ordinal_position
from information_schema.columns
where columnproperty(object_id(table_name), column_name,'IsIdentity') = 1
order by 1,2,3

Quick notes about database normalization

• 1st form:
• No repeated data group
• Has Primary Key
• 2nd form:
• 1st form already
• remove partial dependence (no-key attribute depends on ALL key attributes)
• add link table with primary key
•  3rd form:
• 2nd form already
• remove transitive dependence (no-key attribute depends ONLY on key attributes, nothing else, that is, not depend on other no-key attribute)
• add a new table with FK constraint.
• reference: https://www.youtube.com/watch?v=c7DXeY3aIJw

Database and table size quick overview

--
-- go to any specific database and run
SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

if (object_id('tempdb..#t') is not null) drop table #t

-- check
-- http://technet.microsoft.com/en-us/library/ms188776.aspx
-- for data type confirmation
-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

select name as TableName
, cast((cast(replace(data, 'KB', '') as decimal(15))+cast(replace(index_size, 'KB', '') as decimal(15)))/1024 as decimal(18)) as SizeInMB
, cast(rows as decimal(11)) as RowsCount
from #t
where rows<>'0'
order by 2 desc

-- # of rows.
SELECT SUM(CAST([rows] AS decimal(30,0))) AS [rows]
FROM   #t

DROP TABLE #t

Remove msrepl_tran_version created by SQL Server replication for whole database

declare @replicaitonColumn varchar(256), @rowsCount int, @tableID int, @dropDefaultConstraint varchar(8000), @dropColumnQuery varchar(8000), @debug tinyint, @simulationMode tinyint
select @replicaitonColumn='msrepl_tran_version', @debug=1, @simulationMode=0
declare @TableList table(tableID int, schemaName varchar(256), tableName varchar(256) primary key clustered)
insert into @TableList(tableID, schemaName, tableName)

select row_number() over (order by TABLE_SCHEMA, TABLE_NAME) as tableID
, TABLE_SCHEMA as schemaName
, Table_Name as tableName
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME=@replicaitonColumn
order by TABLE_NAME

if (@debug=1)
begin
select * from @TableList
end

select @rowsCount=@@ROWCOUNT, @tableID=1
if @rowsCount<=0
begin
print 'no replication column found in database, nothing to do'
end else
begin
while @tableID<=@rowsCount
begin
select
-- sample query
-- ALTER TABLE [dbo].[corAccountMaster] DROP CONSTRAINT [MSrepl_tran_version_default_EC5572E6_A47F_4A3A_AC3A_0A8291822756_325732363]
@dropDefaultConstraint='ALTER TABLE '+t.schemaName+'.'+t.tableName+ ' DROP CONSTRAINT '+c.name
from sys.default_constraints c
inner join sys.columns col on col.default_object_id = c.object_id
inner join sys.objects o  on o.object_id = c.parent_object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join @TableList t on t.schemaName=s.name and t.tableName=o.name
where tableID=@tableID
and col.name = @replicaitonColumn

if (@debug=1)
begin
print isnull(@dropDefaultConstraint, '???')
end

if (@simulationMode<>1)
begin
if (@dropDefaultConstraint is not null) exec (@dropDefaultConstraint)
end


select @dropColumnQuery='alter table '+schemaName+'.'+tableName+ ' drop column '+@replicaitonColumn
from @TableList
where tableID=@tableID

if (@debug=1)
begin
print @dropColumnQuery
end

if (@simulationMode<>1)
begin
exec (@dropColumnQuery)
end

set @tableID=@tableID+1
end
end

MSDTC (Distributed Transaction Coordinator)

Few quick notes about MSDTC:

1. The default full path is (C:\Windows\System32\msdtc.exe)
2. To start up, use (Net Start MSDTC)
3. To stop, use (Net Stop MSDTC)
4. Window service name is (Distributed Transaction Coordinator)
5. To configure it properly for sql server, we have to enable network DTC access
1. use (DCOMCNFG)
2. select (Console Root\Component Services\Distributed Transaction Cooridinator\Loacl DTC\Properties\Security\Security Settings)
3. Enable "Network DTC Access" and allow remote clients

T-SQL example to insert result set returned from remote linked server procedure call to a temporary table

declare @parameter1 int, @parameter2 date
select @parameter1=123, @parameter2='2013-08-28'
IF (object_id('tempdb..#TempTable')) is not null
BEGIN
drop table  #TempTable
END
CREATE TABLE #TempTable(col1 int, col2 varchar(50), primary key clustered(col1))
INSERT INTO #TempTable
EXECUTE [LinkedServerName].[DatabaseName].[SchemaName].[usp_StoredProcedure] @parameter1=@parameter1,  @parameter2=@parameter2
SELECT * FROM #TempTable

T-SQL funciton to calculate CUSIP check digit

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetCusipCheckDigit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetCusipCheckDigit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------------------------------------------
-- Author Date
-- Steven Rao 2013-08-08
---------------------------------------------------------------------------------------------------------------
-- Return the check digit for a CUSIP number
---------------------------------------------------------------------------------------------------------------
-- Reference:
-- http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55474df-6908-4d05-bf23-7b341bd7994f/user-defined-function
-----------------------------------------------------------------------------------------------------------------
-- Test script:
--DECLARE @Sample TABLE ( Cusip CHAR(9) );
--INSERT INTO @Sample
--VALUES ('14149YAR9')
-- ,('000020347')
-- ,('126650BG4')
-- ,('254709AC2')
-- ,('437076AQ5') ;
--SELECT *, dbo.[fn_GetCusipCheckDigit](LEFT(Cusip, 8)) as CheckDigit FROM  @Sample ;
-----------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fn_GetCusipCheckDigit]
(
@Cusip CHAR(8)
)
RETURNS CHAR(1)
AS
BEGIN
RETURN
(
SELECT (10 - SUM(s/10 + S%10) % 10) % 10
FROM (
SELECT s=(NULLIF(CHARINDEX(substring(@Cusip, V.number, 1), R.Ref),0) -1) * (2 - V.number % 2)
FROM master..spt_values V CROSS JOIN
(SELECT Ref='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ*@#') R
WHERE V.type='P' and V.number BETWEEN 1 AND 8
) SQ
)
END

GO