util.countrow_view

by 
DECLARE @myTABLE_NAME varchar(64)
DECLARE @myTABLE_SCHEMA varchar(16)
DECLARE @strSQL varchar(max)
 
		DECLARE
		@Loopcount int = 1,
		@RowCount int
 
	SELECT @RowCount = COUNT(*)
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_TYPE = 'VIEW'
 
	WHILE @Loopcount <= @RowCount 
	BEGIN
 
	with myTable as (
	SELECT *,
	ROW_NUMBER() OVER (ORDER BY (SELECT 3)) AS rownumber
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'VIEW')
 
	SELECT @myTABLE_SCHEMA = TABLE_SCHEMA, @myTABLE_NAME = TABLE_NAME FROM myTable WHERE rownumber = @Loopcount
 
	SET @strSQL = 'SELECT ' + char(39) + @myTABLE_SCHEMA + char(39) + ' as [SCHEMA], ' + char(39) + @myTABLE_NAME + char(39) + ' as [TABLE], COUNT(*) as CountRow FROM [' + @myTABLE_SCHEMA + '].[' + @myTABLE_NAME + ']'
	exec(@strSQL)
 
				SET @Loopcount = @Loopcount + 1
	END

Leave a Comment