473,567 Members | 2,902 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored Procedure looks at sysobjects and process info in each table

3 New Member
Hi everyone.

I am fairly new to using stored procedures but for the past few days I've been reading up on them and I need to do the following:

Get all the table names from the sysobjects table
Take all of those names and use them as the table name in the SELECT command
Process each table individually.

I also need to be able to either output a view or have the stored procedure output all the rows that it has processed.

I've done some reading on using cursor and I think thats the way I want to go but I'm really not sure where to go. If anyone has any ideas on how to do this or could just point me in the right direction, it would be greatly appreciated.

Thanks.
Nov 30 '06 #1
5 4105
aaryan
82 New Member
hi zseifts

declare @name varchar(20)
declare sampcursor1 cursor for select name from sysobjects where type = 'U' and sysstat <> 8275
open sampcursor1
fetch next from sampcursor1 into @name
while @@fetch_status = 0
begin
declare @sql varchar(8000),@ from varchar(10)
set @from = @name
select @sql = 'select * from ' + @from
exec(@sql)
fetch next from sampcursor1 into @name
end
close sampcursor1
deallocate sampcursor1
go

hope this will help u to proceed further
Dec 1 '06 #2
iburyak
1,017 Recognized Expert Top Contributor
You did a grate job for a novice.

Your variable name @from is 10 characters long and @name is 20 it cuts names and system doesn't recognize tables.

1. Make them equal @from varchar(20)
2. You don't need @from at all you can use @name instead.
3. Check if you have tables with longer names than 20 in sysobjects and be generous declare @name varchar(100)... . :)


Good luck.
Dec 1 '06 #3
zseifts
3 New Member
Thanks aaryan and iburyak, I'm going try this when I get my test sql server up.
Dec 1 '06 #4
zseifts
3 New Member
Okay, So I've tried to get that SP working that was posted earlier, but its not working. I can get it to loop through once then it would return the info from the first table and none other ones. Anyone have any ideas?

-Zach
Dec 4 '06 #5
iburyak
1,017 Recognized Expert Top Contributor
How do you get the data?
If you run through something like SQL Query analyzer you should be able to see everything.
If through some front end like VB you should request next recordset in a loop to be able to see more.

Did you change size of @from varchar(10) to @from varchar(20).
Run
select name from sysobjects where type = 'U' and sysstat <> 8275
and see how many records you should have in return.
Dec 4 '06 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
9317
by: aaapaul | last post by:
Hallo ! I have a Table with a column "ordernumber" ordernumber A12 A45 A77 A88
4
6227
by: deprins | last post by:
Hello, I have wrote a stored procedure but its real slow. Its activated by a button on web page but its takes to long to process and the web server gives a timeout message after 5 minutes. Is there anyway to speed up this stored procedure? What am I doing wrong here? ...
5
6910
by: Lauren Quantrell | last post by:
Just wondering if this is good form: Alter Procedure "mySPName" @UniqueID int AS set nocount on set xact_abort off DELETE FROM tblNameOne
17
2179
by: serge | last post by:
How can i delete all user stored procedures and all table triggers very fast in a single database? Thank you
0
2507
by: James Hokes | last post by:
Hi All, We're using the 1.1. Framework against SQL Server 2000, and are having a strange issue where we don't get errors back from the stored procedure, i.e. the exception never gets thrown. The scenario is this: 1.) Set up SqlConnection and SqlCommand 2.) Set SqlDataReader equal to the return value from SqlCommand.ExecuteReader
4
4953
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if they are present, making the assumption the database will support the given feature. The problem is I can't find a certain stored procedure in the...
2
5109
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data store. I've done a fair amount of research on concurrency handling in newsgroups and other resources. Below is what I've come up as a standard for...
6
26522
by: Terentius Neo | last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select statement? I mean something like this: Select c.number, call procedure( c.number ) as list from table c With best regards
4
13126
by: gamaz | last post by:
Hi, I am trying to work on a stored procedure that will work with multiple database. I have a prototype of multiple databases. Those are named as the following: ts2_aldkm_app, ts2_aldkp_app, ts2_aldkt_app. The middle part of the database name corresponds to the site name e.g aldkm corresponds to site aldkm etc. Each database has one table...
0
7590
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7905
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8115
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7651
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7960
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6272
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3648
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
930
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.