Script to get row count of all tables – SQL

I’m full of SQL tips lately.  I had a need to get a listing of the row count of all tables in a database.  Here is a nice script I found somewhere that seems to do the trick.

SELECT sysobjects. Name, sysindexes.Rows
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = ‘U’ AND sysindexes.IndId < 2
ORDER BY sysobjects. Name


SQL Express User Instances

How come I’ve never run across this before?  I suspect it’s not something often used, but as I sit here thinking back over my 15+ year career (most of it using SQL Server), I’ve never come across a situation that used this construct.

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Temp\MyDatabase.mdf;Integrated Security=True;User Instance=True


That connection string uses a feature called User Instances.  This opens the database named in the AttachDBFilename attribute in a new instance of SQL Express running under the context of the current user.  Why?  Well this might be useful for users who are not administrators on their machines.  You can’t do this however and attach it to a running instance of SQL Server as you’ll get a sharing violation.  To get at it from Management Studio you’ll have to attach / detach as needed.