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) 

MSSQL ms_foreachdb

When you have many databases and want to iterate a command on them quickly, use the sp_MSforeachdb command. For example:

DECLARE @command varchar(1000) 
SELECT @command = 'SELECT * FROM information_schema.tables' 
EXEC sp_MSforeachdb @command

SQL Merge and Multiple Cases

The Merge feature is new as of SQL 2008, and it’s great for doing exactly that. However, you can only have one when  matched clause, and only one when not matched .


MERGE Mammals AS TARGET
USING(
	SELECT C.LatinName, C.CommonName, C.Lifespan
	FROM Camels C
) AS SOURCE (LatinName, CommonName, Lifespan)
ON TARGET.LatinName=SOURCE.LatinName
WHEN MATCHED THEN
	-- update conditionally. Cases are : normal update, update when newly inactive
	UPDATE SET
	CommonName=
		CASE WHEN SOURCE.LatinName='ABC' THEN 'Brown Camel'
		     WHEN SOURCE.IsActive='DEF' THEN 'Red Camel'
		END
	, Lifespan=SOURCE.Lifespan
WHEN NOT MATCHED THEN
	-- does not exist, new record, insert it
	INSERT (LatinName, CommonName, Lifespan)
	VALUES (SOURCE.LatinName, SOURCE.CommonName, SOURCE.Lifespan );

MS Access Query Truncation Problem

There’s been a small change to the way MS Access works from the ’97 to the 2010 version that can cause some serious issues.

Consider this query “Select * From Table1″ . What happens? In both versions, it will run just fine. However, when importing this command through any database connection, say OLEDB, it won’t work. Instead, what’s fed into 2010 is “able1″.

What’s happening? For database connections, table names are required to be square-bracketed. A simple fix.

Just use “Select * From [Table1]” instead.