Syscomments / Sysdepends
Aug 20, 2007
Quick question relating to the above tables.
I am currently experiencing an issue where the syscomments and sysdepends tables are increasing exponentially.
For example, i have a database with data segment of 666 MB (maybe its the number!) and the syscomments is 254MB and sysdepends is 325MB. all the other tables are remaining static, and over my sample period have not increased. Also there have been no new procedures or views created on this database, so I am not sure why these two tables are increasing so much.
Does anyone have any ideas as to what is causing this? or have any pointers for things i should be checking?
Many thanks in advance.
Mark
View 3 Replies
Mar 29, 2007
I know I shouldn't rely on sysdepends because it's got all sorts of problems with broken dependency chains, etc. But it's better than nothing for finding dependencies, short of rolling your own t-sql parser. So I use sysdepends anyway, with all its faults. I just don't rely on it. anyway, to the point, just an fyi really:
Here's something about sysdepends discovered today that annoys me. If you introduce a dependency of a proc on a table by selecting from the table in the proc, the sql parser discovers this and dutifully adds a row to sysdepends. Very well. But if you do the same select into a temp table, sysdepends doesn't pick up the dependency! Here's what I mean:
use master
go
drop database test_sysdepends
go
create database test_sysdepends
go
use test_sysdepends
go
create table MyTable01 (id int)
go
create proc MyProc01
as
select id from MyTable01
go
create proc MyProc02
as
create table #t (id int)
-- comment this next line out and the dependency is picked up.
-- but as long as we insert into the temp table, we don't pick it up... :(
insert into #t
select id from MyTable01
select id from #t
go
select
so2.name as parent
,so1.name as dependent
from sysdepends d
join sysobjects so1 on d.id=so1.id
join sysobjects so2 on d.depid=so2.id
go
results:
parent dependent
--------- ---------
MyTable01 MyProc01
i am seeing this on 2005 sp1, also on 2000 (msde)
grrr...
www.elsasoft.org
View 3 Replies
View Related
Feb 22, 2007
:shocked: I can't seem to find a way of gettng a list of the view/stored procedure/UDF dependencies by object. Any ideas?
View 6 Replies
View Related
May 30, 2001
Hey Folks,
I am trying to replicate a a development database into a production database.
The production database has a different name from the developmet and I want to
find a way of re-creating the Stored procedures from SYSCOMMENTS.
I keep getting garbled ouput from select text from syscomments.
Can anybody please tell me how to extract Stored Procedures' text
from system table SYSCOMMENTS
TIA
View 2 Replies
View Related
May 19, 2008
Hi,
What is the use of sys.syscomments ?
Cheers,
Mathi
India.
View 5 Replies
View Related
Nov 9, 2000
Hello:
We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.
As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:
"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"
And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.
I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.
Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not
My question is:
1) Is there a way I can read and understand what the data means in sysdepends?
2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?
3) Any other suggestions?
Any information which can be proveded will be greatly appreciated. Thanks.
David Spaisman
View 1 Replies
View Related
May 8, 2008
Has anyone experienced problems calling stored procedures that references syscomments.text from Visual Basic 6?
I have a stored procedure that finds all tables referenced by a specific stored procedure. It works well when I call it from SQL Server Management Studio, but there are cases where it does not find the tables when I call it from VB 6.
I've isolated the failure it to a specific condition in the WHERE clause:
SELECT @howfar = so.id
FROM SYSOBJECTS so
LEFT OUTER JOIN syscomments sc on so.ID = sc.ID
Where lower(so.name) = lower('storedProcName')
AND lower(sc.TEXT) LIKE '%' + lower('update') + ' ' + lower('tableName') + '%'
The call from VB works (finds the record) if
" AND lower(sc.TEXT) LIKE '%' + lower('update') + ' ' + lower('tableName') + '%' "
is omitted, and fails (does not find any records, no error is raised) if that condition is included.
I should also add that the VB Call does work for some combinations of stored procedures and tables.
The old SET NOCOUNT ON trick does not make any difference.
View 4 Replies
View Related
Jul 14, 1999
I have a SQL6.5 sp5a, NT4 sp5, BDC, IE5 server that I have installed SQL7
on. My installation is set to mixed security.
Everytime I try to run the upgrade wizard I get the following error/problem
right after the "Checking 6.x syscomments for corruption and Verifying 6.x
data base logins etc.
Summary of warnings (if any) and choices:
=============================================
The following syscomments entries are invalid on your 6.x SQL Server. It is
recommended that you fix these problems before you continue.
=============================================
********************************
master
********************************
********************************
Inconsistency Report For Database: master.
********************************
dbo.sp_db_upgrade2
Procedure
Occurs 28 times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_fallback_activate_svr_db
Procedure
Occurs three times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_password
Procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_recompile
Procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_rename
Procedure
Occurs 11 times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
=============================================
The following script will be created as upgrade.ini and run into the script
interpreter when you press finish.
=============================================
[Options]
Method=1
Perform a CRC Check=0
Source=CCIDEV
Destination=CCIDEV
Export Server Startup Options=
Import Server Startup Options= -T1808
SQLServer70Path=C:MSSQL7SourcePassword=066ce2f9c0e 4ba
DestinationPassword=00
MasterPath=C:MSSQLDATAMASTER.DAT
VerswitchOldVer=65
Code Page=1252
Override Server Code Page=0
QuotedIdentifiers=0
ObjectDumpPath=c:mssql7upgradeCCIDEV_071399_163117 Ansi Nulls=0
[Tasks]
Export and Import via Named Pipe=0
Marking database upgrade status=0
Export Logins=0
Export Database Owners=0
Import Logins=0
Creating Databases=0
Setting Database Options=0
Export Database Objects=0
Import Database Objects=0
Export SQL Executive Settings=0
Preparing MSDB for Upgrade=0
Import SQL Executive Settings=0
Export Server Settings from Master=0
Modifying Scripts=0
Import Server Settings from Master=0
Preparing SQL-DMO for upgrade=0
[Databases]
006CCIDATA=0
003model=0
005msdb=0
001master=0
I don't find anything about this anywhere except the upgrading to SQL7 white
paper, it mentions that the text for objects must be intact in the
syscomments system table and that logins must exist for every database user,
but that doesn't really tell me how to fix this.
Has any one seen these errors or can point me
to where on Microsoft's site you can now EASILY get KB info for SQL Server
7?
View 2 Replies
View Related
Oct 18, 2006
Hello,
I have a table in SQL Server 2000 which has few triggers. When I try to update a record in that table, I get following message:
Warning: The table '[TABLE_NAME]' has been created but its maximum row size (17275) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
where the data I am updating is well less than the field limit. The triggers are stored in syscomments table and when I dropped these triggers, the update statements were executed without this message. Is there anyway I can change the data type of field text in syscomments from nvarchar to ntext?
Thanks
Din
View 1 Replies
View Related
