Author |
Topic |
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-09-07 : 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 8There is already an object named 'isp_allTableSize' in the database.Server: Msg 208, Level 16, State 1, Line 18Invalid object name 'master..TablesTemp'.Server: Msg 208, Level 16, State 1, Line 63Invalid object name 'master..TablesTemp'.------------------------------------------------------------------------ TRUNCATE table TablesTemp-- drop proc dbo.isp_allTableSizeCREATE PROC dbo.isp_allTableSize asSET 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..DatabaseTablesDECLARE @date DATETIMESELECT @date = DATEDIFF(mi, currentdate, GETDATE()) FROM master..databaseTablesIF (@date > 7)BEGINTRUNCATE TABLE master..databaseTablesEND*/DELETE master..databaseTables WHERE DATEDIFF(DAY, currentdate, GETDATE()) > 7DECLARE @db VARCHAR(60) DECLARE @sql VARCHAR(8000) DECLARE icr_getDatabases CURSOR FOR SELECT top 1 name FROM master..sysdatabases where name <> 'tempdb' OPEN icr_getDatabasesFETCH 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_getDatabasesDEALLOCATE icr_getDatabasesINSERT master..DatabaseTables SELECT *, GETDATE() FROM master..TablesTemp-- TRUNCATE table DatabaseTables-- SELECT * from master..DatabaseTablesDROP TABLE master..TablesTemp =============================http://www.sqlserverstudy.com |
|
pootle_flump
1064 Posts |
Posted - 2007-09-07 : 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 8There is already an object named 'isp_allTableSize' in the database.
You can't really be serious can you? |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-07 : 10:52:18
|
because the error message tells you VERY clearly what is wrong._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-09-07 : 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
11752 Posts |
Posted - 2007-09-07 : 11:00:17
|
yes it can._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-09-07 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-09-07 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-09-07 : 13:39:29
|
I will be forced to taunt you a second time! |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-09-07 : 14:14:44
|
/me patiently waits for the Peso +1 [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
|
|