Select all tables, and number of rows and columns

SELECT 
 t.NAME AS TableName,
 p.[Rows],
 count(c.name) as Columns
FROM 
 sys.tables t
INNER JOIN 
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
 sys.columns c on t.OBJECT_ID=c.OBJECT_ID
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND 
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
 object_name(i.object_id) 

Disable PK/FK Contraints For Easier Data Migration

If you are copying mass amounts of data, doing updates, or moving data, Primary and Foreign keys can get in the way.

You can disable them like so :

USE [Northwind]
GO

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'  

And then remember to re-enable them later.

USE [Northwind]
GO

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? ENABLETRIGGER ALL'  

MS SQL : Cannot Connect to Database After Move

When moving databases from one sever to another, sometimes the users can become locked out. What I mean by this is, the user technically is in the Security>Users folder with all of the correct permissions like they should, but they cannot connect to the server, or they cannot connect to the database itself.

The solution to this is to delete the user, and remake it again.

SQL Scope_Identity() Gotcha


PROCEDURE [dbo].[SaveInfo]
(
-- ... parameters ...
)
AS
INSERT INTO Table1 ( ... ) Values ( ... )
RETURN SCOPE_IDENTITY()

This code doesn’t quite do what you think it does. If the insertion goes successfully, it returns the ID of the newly inserted row. But if not, what is returned is actually the ID of the last known record. If you are depending on this ID, use transactions for saftey; or alternatively, try-catch.