How to count quickly the rows in all tables?
DECLARE @CountStats TABLE(SchemaName SYSNAME,
TableName SYSNAME,
RowsCount INT )
INSERT @CountStats
EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 2), PARSENAME(''?'', 1), COUNT(*) FROM ?'
SELECT * FROM @CountStats ORDER BY RowsCount DESC
Generate Sequence Number
WITH cteNumber AS
(SELECT Number=ROW_NUMBER() OVER(ORDER BY a.name)
FROM sys.objects a,sys.objects b)
SELECT * FROM cteNumber
Disable all triggers and constraints in a database
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
-- SQL disable all constraints - disable all constraints sql server
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
--Selected Table
ALTER TABLE Production.Product ENABLE TRIGGER ALL
Execute Dynamic SQL
DECLARE @TableName sysname ='Sales.SalesOrderHeader'
EXECUTE ('SELECT * FROM '+@TableName) -- T-SQL string with variable –
How to list all primary and foreign keys in a database?
