Combining this code into a sproc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining this code into a sproc

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-21 : 12:41:11

expounding on a previous entry by me. At this stage, i need to figure out how to incorporate the code below into the sproc which follows. The intent is, after gathering the database stats it will gather the stats on individual tables and plot them into another table, and also link them to the db stats for the day it runs. For example, if we run the db stats for Arrest today, I would want to be able to look into the table with the table statistics and pull all the table stats for that particular day, so I have the db and table stats for that day.

Code:
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t

-- # of rows.
SELECT DB_NAME() as DBName, GETDATE() as DateCreated, SUM(CAST([rows] AS int)) AS [rows]
INTO SIZING_DB.dbo.RowCounts
FROM #t

DROP TABLE #t

SPROC:
USE [SIZING_DB]
GO
/****** Object: StoredProcedure [dbo].[STATS] Script Date: 11/21/2011 11:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[STATS]
WITH
EXECUTE AS CALLER
AS
INSERT INTO arrest.dbo.dbstats
SELECT GETDATE() AS date_and_time,
DB_NAME() AS database_name,
SUM(total_pages) AS reservedKB,
SUM(used_pages) AS usedKB,
SUM(total_pages -used_pages) AS unallocatedKB
FROM sys.allocation_units
INSERT INTO arrest.dbo.dbstats2
SELECT GETDATE() AS date_and_time,
DF.name,
SIZE,
FILEPROPERTY(DF.name, 'spaceused') AS pages_used,
FILEPROPERTY(DF.name, 'spaceused') * 1.0 / SIZE * 100 AS pct_used
FROM sys.database_files AS DF

SELECT dbstats.uniquekey,
dbstats.date_and_time,
dbstats.database_name,
dbstats.reservedKB,
dbstats.usedKB,
dbstats.unallocatedKB,
dbstats2.size,
dbstats2.pages_used,
dbstats2.pct_used
FROM ARREST.dbo.dbstats dbstats
INNER JOIN
ARREST.dbo.dbstats2 dbstats2
ON (dbstats.uniquekey = dbstats2.uniquekey)

Again, any guidance is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 12:45:03

so you want it to return both the resultsets?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-21 : 12:58:27

I do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 13:07:36

[code]

SPROC:
USE [SIZING_DB]
GO
/****** Object: StoredProcedure [dbo].[STATS] Script Date: 11/21/2011 11:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[STATS]
WITH
EXECUTE AS CALLER
AS

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t

-- # of rows.
SELECT DB_NAME() as DBName, GETDATE() as DateCreated, SUM(CAST([rows] AS int)) AS [rows]
INTO SIZING_DB.dbo.RowCounts
FROM #t

DROP TABLE #t

INSERT INTO arrest.dbo.dbstats
SELECT GETDATE() AS date_and_time,
DB_NAME() AS database_name,
SUM(total_pages) AS reservedKB,
SUM(used_pages) AS usedKB,
SUM(total_pages -used_pages) AS unallocatedKB
FROM sys.allocation_units
INSERT INTO arrest.dbo.dbstats2
SELECT GETDATE() AS date_and_time,
DF.name,
SIZE,
FILEPROPERTY(DF.name, 'spaceused') AS pages_used,
FILEPROPERTY(DF.name, 'spaceused') * 1.0 / SIZE * 100 AS pct_used
FROM sys.database_files AS DF

SELECT dbstats.uniquekey,
dbstats.date_and_time,
dbstats.database_name,
dbstats.reservedKB,
dbstats.usedKB,
dbstats.unallocatedKB,
dbstats2.size,
dbstats2.pages_used,
dbstats2.pct_used
FROM ARREST.dbo.dbstats dbstats
INNER JOIN
ARREST.dbo.dbstats2 dbstats2
ON (dbstats.uniquekey = dbstats2.uniquekey)

SELECT * FROM SIZING_DB.dbo.DBTableStats
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-22 : 09:05:20

I'm still having trouble with this code. The purpose of this script (if it isn't obvious) is to pull the data on table size usage from all tables in the various dbs on the server. That data goes into the temp table during that execution of the sproc. It is then intended to insert that data into the sizing_db.dbo.dbtablestats for another select statement to pull it from there. the sproc resides in the master db as a system sproc. For some reason, which i cannot figure out, when i execute the sproc it complains that "there is already an object named 'DBTsableStats' in the database". Well of course there is! Why is this a problem. Also, the code had to be changed from the one above to correct some of the db names that were changed.
Any help is appreciated.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[STATS] Script Date: 11/21/2011 11:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[sp_GetSTATS]
WITH
EXECUTE AS CALLER
AS

CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t

-- # of rows.
SELECT DB_NAME() as DBName, GETDATE() as DateCreated, SUM(CAST([rows] AS int)) AS [rows]
INTO SIZING_DB.dbo.RowCounts
FROM #t

DROP TABLE #t

INSERT INTO sizing_db.dbo.dbstats
SELECT GETDATE() AS date_and_time,
DB_NAME() AS database_name,
SUM(total_pages) AS reservedKB,
SUM(used_pages) AS usedKB,
SUM(total_pages -used_pages) AS unallocatedKB
FROM sys.allocation_units
INSERT INTO sizing_db.dbo.dbstats2
SELECT GETDATE() AS date_and_time,
DF.name,
SIZE,
FILEPROPERTY(DF.name, 'spaceused') AS pages_used,
FILEPROPERTY(DF.name, 'spaceused') * 1.0 / SIZE * 100 AS pct_used
FROM sys.database_files AS DF

SELECT dbstats.uniquekey,
dbstats.date_and_time,
dbstats.database_name,
dbstats.reservedKB,
dbstats.usedKB,
dbstats.unallocatedKB,
dbstats2.size,
dbstats2.pages_used,
dbstats2.pct_used
FROM sizing_db.dbo.dbstats dbstats
INNER JOIN
sizing_db.dbo.dbstats2 dbstats2
ON (dbstats.uniquekey = dbstats2.uniquekey)

SELECT * FROM sizing_db.dbo.DBTableStats ds

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:42:08

For some reason, which i cannot figure out, when i execute the sproc it complains that "there is already an object named 'DBTsableStats' in the database".

thats because of below statement


SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t

to avoid error add a check like below before it
...
IF OBJECT_ID('SIZING_DB.dbo.DBTableStats') IS NOT NULL
DROP TABLE SIZING_DB.dbo.DBTableStats

SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-22 : 09:53:46

Thank you. i see how that is working now. However, i think it's causing the DBTableStats table to not serve the purpose it's intended. The intent of that table is to continue to store the data that is produced by each execution of that sproc. If it's not possible to append that data with each new exec, then would it be possible or advisable to insert the data from dbtablestats into yet another table, appending with each insert?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:58:32

if you want to append data in it you need to replace


SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t

with

INSERT into SIZING_DB.dbo.DBTableStats
SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
FROM #t

and you need to create this table at first using CREATE TABLE statement with correct structure of select statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-22 : 10:26:10

do i need an "if exists" statement prior to creating the DBTableStats table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:48:02

quote:
Originally posted by WJHamel

do i need an "if exists" statement prior to creating the DBTableStats table?

nope. no need. also now that you're creating table beforehand you can remove that IF OBJECT_ID() check also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-22 : 10:59:21

But if the create table statement exists in the sproc without an If Exists, or If not exists statement, then each time i run the sproc it is going to try to create that table again, even though it already exists, and throw the "There is already an object named 'DBTableStats' in the database. Which is what it is doing now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 11:12:36

quote:
Originally posted by WJHamel

But if the create table statement exists in the sproc without an If Exists, or If not exists statement, then each time i run the sproc it is going to try to create that table again, even though it already exists, and throw the "There is already an object named 'DBTableStats' in the database. Which is what it is doing now.

why should you create it inside proc?
its anyways a permanent table so you should be creating it by a one time script outside proc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-22 : 12:23:51

Got it. All is well now. Thank you again for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 12:38:29

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources