Record Count for Tables

/******************************************************************************/
/*  SQL_TABLE_COUNTs.SQL                                                      */
/*                                                                            */
/*  Script to go through all XXXX schema tables in the SQL DB and             */
/*  get a count of the number of columns it holds.                            */
/*                                                                            */
/*  HISTORY                                                                   */
/*  DATE     INITIALS   COMMENTS                                              */
/*  070416   KVA        Initial Creation.                                     */
/******************************************************************************/
use AdventureWorks
GO

--This table must be created prior to running the script
create table dbo.temp_table_count 
(schemaName varchar(10),
 tableName varchar(35),
 count int);


BEGIN
	DECLARE         @lv_table_name	varchar(35),
			@lv_schema_name varchar(10),
			@lv_count		int,
			@lv_count_str	varchar(15),
		        @lv_sql_stmt	varchar(400)
	DECLARE cur_tables CURSOR FOR
	select s.name, t.name
	from sys.tables t, sys.schemas s
	where t.schema_id = s.schema_id
      and s.name = 'XXXX'
	ORDER BY t.name;

	BEGIN

		create table ##temp_count (rec_id int,mycount int)
		insert into ##temp_count values (1,0)

		OPEN cur_tables
		FETCH NEXT FROM cur_tables INTO @lv_schema_name, @lv_table_name
		WHILE @@FETCH_STATUS = 0
		BEGIN

			select @lv_sql_stmt = 'update ##temp_count set mycount = (select count(*) from xxxx.' + @lv_table_name + ') where rec_id=1'
			execute (@lv_sql_stmt)

			select @lv_count = mycount from ##temp_count where rec_id = 1
			select @lv_count_str = STR(@lv_count)

			select @lv_sql_stmt = 'insert into dbo.temp_table_count values ( ''XXXX'', ''' + @lv_table_name + ''', ' + @lv_count_str + ')'
			execute (@lv_sql_stmt)
			
			FETCH NEXT FROM cur_tables INTO @lv_schema_name, @lv_table_name
		END

		drop table ##temp_count
		CLOSE cur_tables
		DEALLOCATE cur_tables

	END
END