List All Primary Key Columns Within a Database

SELECT
schema_name(ta.schema_id)  SchemaName
,ta.name  TableName
,col.name  ColumnName
,indcol.key_ordinal IndexPositoin
,ind.name IndexName
,ind.type_desc IndexType
,ind.fill_factor Fill_Factor
from sys.tables ta inner join sys.indexes ind
on ind.object_id = ta.object_id
inner join sys.index_columns indcol
on indcol.object_id = ta.object_id
and indcol.index_id = ind.index_id
inner join sys.columns col
on col.object_id = ta.object_id
and col.column_id = indcol.column_id
where ind.is_primary_key = 1
order by 1,2,4

List of all FK in a database

SELECT
OBJECT_NAME(referenced_object_id) 'Referenced table'
,OBJECT_NAME(parent_object_id) 'Parent table'
,cref.NAME 'Referenced column name'
,c.NAME 'Parent column name'
FROM sys.foreign_key_columns fkc INNER JOIN sys.columns c
ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.object_id
INNER JOIN sys.columns cref
ON fkc.referenced_column_id = cref.column_id AND fkc.referenced_object_id = cref.object_id
--where   OBJECT_NAME(parent_object_id) = 'tablename'
order by 1, 2

SSIS quick notes

http://www.bidn.com/blogs/Daniel/ssas/1361/ssis-blocking-non-blocking-and-partially-blocking-transformations
http://www.developer.com/db/top-10-common-transformations-in-ssis.html

A Non Blocking transformation is a dam that just lets the water spill over the top
A Blocking transformation is a dam that lets nothing through until the entire volume of the river has flowed into the dam

Synchronous are components like the Conditional Split or Derived Column Transformation where rows flow into memory buffers in the transformation and the same buffers come out. 

Asynchronous transformation has two types, fully blocking and partial blocking.

Synchronous = non blocking. input and output use same memory buffer
Asynchronous = partial blocking and blocking. Need new memory buffer for output
blocking = no output until all input memory are processed

Merge Join is similar to database join
Merge needs input to be sorted but Union All does not requires this

Non Blocking

• Audit
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Derived Column
• Import Column
• Lookup
• Multicast
• Percentage sampling
• Row count
• Row sampling
• Script component

Partially Blocking

• Data mining
• Merge
• Merge Join
• Pivot/Unpivot
• Term Extraction
• Term Lookup
• Union All

Blocking

• Aggregate
• Fuzzy Grouping
• Fuzzy Lookup
• Sort

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