USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_DB_TblRowCOUNT]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DB_TblRowCOUNT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_DB_TblRowCOUNT]
GO

CREATE Procedure [dbo].[usp_DB_TblRowCOUNT]
AS 

/******************************************************************************
**		File: usp_DB_TblRowCOUNT.sql
**		Name: usp_DB_TblRowCOUNT
**		Desc: This procedure counts the number of rows in each user table for current database.
**
**		Return values:
**
**		Called by: SSIS for row count
**
**		Parameters:
**		Input							Output
**		----------					-----------
**
**		Auth:swarns
**		Date: 06/24/2010
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:			Description:
**		----------	-------------	----------------------------------------------
**		07/02/2010      swarns          Added DBSize functionality
**		
**********************************************************************************/


BEGIN
set nocount on
                --Declare temp table
declare @temp table
(
Report_Date varchar(100)
,Table_Name varchar(100)
,Num_Rows varchar(100)
)

--populate header
insert into @temp
select 
QUOTENAME('REPORT_DATE','"'), QUOTENAME('TABLE_NAME','"'), QUOTENAME('NUM_ROWS','"')

--Populate DB size
insert into @temp
SELECT 
QUOTENAME(UPPER(REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-')),'"'),
quotename('DBSIZE','"'),
      cast(sum(cast(Fileproperty(name,'SpaceUsed') as numeric (18,2))*8/1024) as numeric(18,2)) AS spaceused
FROM   sysfiles

--populate table rows
insert into @temp
SELECT 
                                QUOTENAME(UPPER(REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-')),'"') as [DateChecked]
                                , QUOTENAME(so.[name],'"') as [TableName]
                                , QUOTENAME(rows,'"') as RecordCount
    FROM 
                                sysindexes si
        INNER JOIN sysobjects so ON si.id = so.id
    WHERE 
                                si.indid < 2 
        AND so.type = 'U' 
        AND so.[name] != 'dtproperties'
        AND so.[name] not like 'sys%'
    ORDER BY so.[name]    


select * from @temp
END 

GO