You can either use dynamic SQL or undocumented stored procedure sp_MSforeachtable http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

>I am trying to atomate getting the count of rows in each user table in a given database. I tried the following, but got an error saying the table variable needs to be declared. ?? I assume from this that a variable can not be put in the 'from' clause. Is there another way to do this?
>
>

>DECLARE @TableName	varchar(80)
>
>DECLARE TableInfo CURSOR
>FOR
>SELECT [Name]
>FROM sys.tables
>OPEN TableInfo
>FETCH NEXT FROM TableInfo INTO @TableName
>WHILE @@fetch_status = 0
>BEGIN
>	Select @TableName, count(*) From @TableName
>
>	FETCH NEXT FROM TableInfo INTO @TableName
>END
>CLOSE TableInfo
>DEALLOCATE TableInfo
>