dbo.usp_DB_TblRowCOUNT.StoredProcedure.sql
4.97 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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