Some of the best sql free tools

http://www.johnsansom.com/top-10-free-sql-server-tools/

Some of the best sql free tools

http://www.johnsansom.com/top-10-free-sql-server-tools/

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