A veces me tengo que enfrentar «a ciegas» a extraer datos de una BD para pasarlos a otra con una estructura distinta (por ejemplo, migraciones de un software a otro). Una de las complejidades de esto es saber qué hay que traspasar, a veces te encuentras una base de datos con 500 tablas y lógicamente no vas a revisarlas una por una. Hay varias formas de comprobar el estado de las mismas, yo suelo utilizar esta consulta que ya tengo guardada, donde usando el procedimiento almacenado del sistema sp_MSforeachtable puedo listar los nombres de todas las tablas y, junto a este, el número de filas que contiene, de esa forma ya descarto todas las tablas que no tengan datos:

--Ponemos en uso nuestra BD
USE NombreDeNuestraBD

--Variable de tabla que almacenará los datos
DECLARE @CuentaFilas TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;

--Consulta sirviéndonos del procedimiento almacenado
INSERT INTO @CuentaFilas ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;

--Visualizamos los datos, en mi caso los ordeno por cantidad de datos
--porque las que tengan 0 o 1 filas no me interesan. Ordenad como queráis.
SELECT [TableName], [RowCount]
FROM @CuentaFilas 
ORDER BY [RowCount] desc