The ALTER TABLE statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database , table , column

Error message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database , table , column

Explanation:

Some foreign values do not exist in primary table.

Solution:

Make your table empty, or add the associated value to the primary key table.

A query to find the missing values:

SELECT F.* FROM ForeignTableName F

LEFT JOIN PrimaryTableName P on F.FKcolumn = P.PKcolumn

WHERE P.PKcolumn is null

Search for a text including special characters in SQL Server

1. Delimits the special character with square brackets:

Examples:

for '%' we will write:

SELECT * FROM TableName WHERE ColumnName LIKE '%[%]%'

for the text 'UPDATE [TABLENAME]' , that already contain square brackets, we will delimit the open bracket:

select o.name from ..... where text like '%UPDATE [[]TABLENAME]%'

2. Use a custom escape character:

Examples:

for '%' we will write:

SELECT * FROM TableName WHERE ColumnName LIKE '%\%%' ESCAPE '\'

and for 'UPDATE [TABLENAME]' we will write:

select o.name from ..... where text like '%\UPDATE [TABLENAME]%' ESCAPE '\'

Note: you can protect the code from SQL Injection using ESCAPE: 

http://copypastenet.blogspot.com/2010/11/protect-from-sql-injection.html

IF UPDATE

IF UPDATE (ColumnName)

Uses in Triggers: return True if the column was updated.

Note: An update means that the query has SET the value of the column:

UPDATE TableName SET ColumnName = ColumnName --> UPDATE (ColumnName)=TRUE

UPDATE TableName SET ColumnName = 10 --when ColumnName already =10 --> UPDATE (ColumnName)=TRUE

Get Dependencies of a table

select distinct sd.id, so.name, so.Type, sd.depid, so_dep.name, so_dep.Type

from  sys.sysdepends sd

inner join sys.objects so on so.object_id = sd.id 

and SCHEMA_NAME(so.schema_id) = 'dbo'

inner join sys.objects so_dep on so_dep.object_id = sd.depid 

where so_dep.name = 'TABLENAME'

order by so.name

Understanding SQL Dependencies:

http://msdn.microsoft.com/en-us/library/ms345449.aspx

Select the last IDENTITY

SELECT @@IDENTITY will return the last identity value entered into a table in your current session.

SELECT SCOPE_IDENTITY() will return the last identity value created in the current session, in the current scope.

SELECT IDENT_CURRENT(‘TABLENAME’) will return the last identity value entered into tablename in your current session, and not only in the current scope.

When using @@IDENTITY, situation that another user will insert newer identity than us can occured, and this can cause us some problems...

@@IDENTITY - current session.

SCOPE_IDENTITY() - current session + current scope.

IDENT_CURRENT(‘TABLENAME’) - current session + specific table.

Select triggers of a table

SELECT name,OBJECT_NAME(parent_object_id) 

FROM sys.objects 

WHERE type = 'TR' 

AND SCHEMA_NAME(schema_id) = 'dbo'

AND OBJECT_NAME(parent_object_id) = 'TABLENAME'

List of tables with data

SELECT SCHEMA_NAME(schema_id) as SchemaName, 

so.name as TableName, 

MAX(si.rows) as Row_Count

FROM sys.objects so 

INNER JOIN sysindexes si on si.id = so.object_id 

WHERE so.type = 'U' 

GROUP BY so.object_id, so.name, so.schema_id

HAVING MAX(si.rows) > 0