| Author |
 Topic  |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
 Posted - 09/07/2007 : 10:34:44
|
Hi, Trying to create a SP. But getting an error msg. Any ideas how to fix it? My SP gets the size of the tables in every DB.
Server: Msg 2714, Level 16, State 5, Procedure isp_allTableSize, Line 8
There is already an object named 'isp_allTableSize' in the database.
Server: Msg 208, Level 16, State 1, Line 18
Invalid object name 'master..TablesTemp'.
Server: Msg 208, Level 16, State 1, Line 63
Invalid object name 'master..TablesTemp'.
----------------------------------------------------------------------
-- TRUNCATE table TablesTemp
-- drop proc dbo.isp_allTableSize
CREATE PROC dbo.isp_allTableSize as
SET NOCOUNT ON
CREATE TABLE master.[dbo].[TablesTemp] (
[DatabaseName] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reserved] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Data] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexSize] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unused] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
/*
-- SELECT COUNT (*) from master..DatabaseTables
DECLARE @date DATETIME
SELECT @date = DATEDIFF(mi, currentdate, GETDATE())
FROM master..databaseTables
IF (@date > 7)
BEGIN
TRUNCATE TABLE master..databaseTables
END
*/
DELETE master..databaseTables
WHERE DATEDIFF(DAY, currentdate, GETDATE()) > 7
DECLARE @db VARCHAR(60)
DECLARE @sql VARCHAR(8000)
DECLARE icr_getDatabases CURSOR FOR
SELECT top 1 name FROM master..sysdatabases where name <> 'tempdb'
OPEN icr_getDatabases
FETCH icr_getDatabases INTO @db
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'USE [' + @db + ']
DECLARE @table_name VARCHAR(60)
DECLARE @cursor VARCHAR(8000)
DECLARE icr_getTables cursor for
SELECT table_name from information_schema.tables
WHERE table_type = ''base table''
AND table_schema = ''dbo''
AND table_name NOT LIKE ''dbo.%''
OPEN icr_getTables
FETCH NEXT FROM icr_getTables into @table_name
WHILE @@fetch_status = 0
BEGIN
INSERT master..TablesTemp
EXEC sp_spaceused @table_name
FETCH NEXT FROM icr_getTables INTO @table_name
END
CLOSE icr_getTables
DEALLOCATE icr_getTables'
EXEC (@sql)
FETCH NEXT FROM icr_getDatabases INTO @db
END
CLOSE icr_getDatabases
DEALLOCATE icr_getDatabases
INSERT master..DatabaseTables
SELECT *, GETDATE() FROM master..TablesTemp
-- TRUNCATE table DatabaseTables
-- SELECT * from master..DatabaseTables
DROP TABLE master..TablesTemp
=============================
http://www.sqlserverstudy.com
|
Edited by - funketekun on 09/07/2007 10:35:12
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 09/07/2007 : 10:50:43
|
quote:
Originally posted by funketekun
Hi, Trying to create a SP. But getting an error msg. Any ideas how to fix it? My SP gets the size of the tables in every DB.
Server: Msg 2714, Level 16, State 5, Procedure isp_allTableSize, Line 8
There is already an object named 'isp_allTableSize' in the database.
You can't really be serious can you? |
 |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 09/07/2007 : 10:52:18
|
because the error message tells you VERY clearly what is wrong.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 09/07/2007 : 10:55:36
|
I have the SP created. Now it doesn't run the code inside..
can a nested cursor be run in a SP?
=============================
http://www.sqlserverstudy.com
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 09/07/2007 : 11:02:03
|
I found the error, too. Don't post anything new, I want to go make some popcorn so I can enjoy the show!
Jim
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 09/07/2007 : 11:28:52
|
Pardon my post funketun, I jumped in too fast. Sorry (even tho' the time doesn't show it)
Jim |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 09/07/2007 : 13:39:29
|
| I will be forced to taunt you a second time! |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2113 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 09/07/2007 : 14:16:30
|
bwwwwwwwwahahahaha
SET @sql = 'USE [' + @db + ']
OK, that's not nioce.
This is what he wants
quote:
My SP gets the size of the tables in every DB.
Funk, instead of fixing what you gave us, can we show you have this should be done?
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
|
Edited by - X002548 on 09/07/2007 14:20:05 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
|
| |
Topic |
|
cannot create SP ( error 2714 )
