print list of queries, tables, views and sp

I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?What do you mean by print?
What version of SQL Server are you running? This will have an effect on the query you need to run. The below example was written for 2000

SELECT name
, id
, type
FROM sysobjects
WHERE type IN ('V', 'U', 'SP', 'FN')
-- v = view, u = table, sp = sproc, fn = user-defined function|||2005 Users Note:
BOL Says
Important: This Microsoft SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use catalog views instead.

Any general comments as to whether we should still be coding with sysobjects ?

:angel:

GW|||We are kind of caught on the edge of the sword on this issue. Because users rarely give us enough information to know what version of SQL they are using, we tend to give them the answers that work under the largest possible set of conditions.

You are correct, using the catalog views is preferable if you are running a version of SQL Server that supports the catalog views. On a "going forward" basis, you probably ought to only use the catalog views, but on a "forum answer" I tend to stick with what will work for the largest number of people.

-PatP|||Anybody have the catalog solution to hand?

I don't get to play on much 2K5, but I am going to be taking my MCTS in it in a couple of months, so I should really get brushed up on it :p|||Play around with the view sys.objects. You should have it in no time. I think id changed to object_id, but most of the rest is the same.|||SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES

SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES-PatP

'primary file was full' claimed by job when there is plenty space to grow

Hi,
Working on sql server 2000.
I have a job that pulls data from one server into this
server's database. It failed last time claiming the
primary file of the db was full while there were over 24
gig room to grow and the job will only pull in less than 2
gig data.
Then, I run it again serveral hours later, and everything
went through fine.
What happened? Ever encountered this wierd problem
yourself?
Many thanks.
JJCheck out below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
Tibor Karaszi
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:07ab01c3a258$9cf1a610$a301280a@.phx.gbl...
> Hi,
> Working on sql server 2000.
> I have a job that pulls data from one server into this
> server's database. It failed last time claiming the
> primary file of the db was full while there were over 24
> gig room to grow and the job will only pull in less than 2
> gig data.
> Then, I run it again serveral hours later, and everything
> went through fine.
> What happened? Ever encountered this wierd problem
> yourself?
> Many thanks.
> JJ

Primary File Group Full

Weird. I have an Agent job that populates some warehouse data every night. It's been working fine for over a year. Then it fails with a message that thePRIMARY file group is full. The database is set to Simple recovery, automatic, unrestricted growth by 10% for both the log file and the data file. The drive it's on has 96GB free. The data file is 1.5GB and the log is 2MB and there's not very much fluctuation in the amoutn of data going into it.

Anybody have an idea why that would happen?

Thanks for any insight,
Pete

You're probably timing out when it hits the autogrow. As ageneral rule, you should never rely on auto-growth! Instead, keepyour databases a lot bigger than you think you'll need. And setthe auto-grow rate to a very small amount (maybe 5-10 megs, some amountthat can grow within a few seconds). You have 96 GB free on thedrive -- why not set your databases to 10 or 20 GB?
And make sure you keep an eye on them. Keep growing them as they fill up.

|||

That's what I suspected since all the settings appear correct and the only activity in the job is just a huge number of inserts. I figured that at the point of needing to grow, there's a pile-up of inserts trying to happen. The problem with increasing the size significantly is that it's anything but the most important database on that server and I wouldn't want to limit the others just for this one. I can give it a little boost, though. Thanks for the help.

Pete

|||At the least, set a smaller auto-grow threshold so that it won't haveto wait -- it should take very little time to grow 5 or 10 megs. Note that this can contribute to fragmentation, which is why I suggestgrowing manually, in much larger chunks.

Preventing schedule job to run

Hi,
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance ... jVery simply... first step in Job A could be to update a flag on the DB, last step would be to remove it. First step in Job B would be to check the flag, if present it bombs if it's not there then continue.|||If you mean to have Job B not run at all, and let Job A potentially run twice in a row, then you could do it this way:

Create a table in say the pubs database called Status (col1 varchar(10))

The first step in Job A will be to put a row into this table. The last step in Job A will be to delete the table (think of it as an on/off switch)

The first step in Job B would be a check to see if a row exists, and if it does, then select 1/0 (or any other error of your choice that will trigger the OnJobStepFailure to trigger.

Set the failure action of step 1 of Job B to be exit the job, then you are done.

Not too sure what you would have to do to get Job B to run at say 2:30 or so, if you can not have Job A run twice in a row.

Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Yeah, you can do that, just do the following...

USE msdb
UPDATE sysjobs
SET enabled = 1
WHERE jobid = <jobid>

to enable the job and then set it back to 0 to disable it.

Another option would be to add job B as a step of job A. And just let it always run as soon as job A finishes. Not sure if that is an option though.

Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Sure... use the method Kuthula posted above, only instead of a flag in a table, have job A disable job B as the first step, and then enable it as the last step. using the update to sysjobs in msdb.