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)
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'
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.
-- ... parameters ...
INSERT INTO Table1 ( ... ) Values ( ... )
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.
SQL Null String Concatenation
SELECT ID, FieldOne + COALESCE( '/' + OptionalFieldTwo, '' ) as concatenatedString FROM MyTable
SQL Select Case
SELECT CASE @MyVariable WHEN 1 THEN [Col_1] WHEN 2 THEN [Col_2] ... WHEN 10 THEN [Col_10] END