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_namevarchar(35),
@lv_schema_name varchar(10),
@lv_countint,
@lv_count_strvarchar(15),
        @lv_sql_stmtvarchar(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