I want Know this In the Back End Side - Bandi [Resolved]

There are multiple ways to list out the procedure names in a database

1. using sys.procedures :

USE DatabaseName
GO
SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), name, create_date, modify_date FROM sys.procedures ;

2. using sys.all_objects

SELECT NAME FROM DatabaseName .SYS.ALL_OBJECTS WHERE type='P'

--or

USE DatabaseName 
GO
SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='P'

3. using sys.sql_modules

SELECT DISTINCT 
       o.name AS Object_Name,
       o.type_desc, m.definition
  FROM sys.sql_modules m 
  INNER JOIN sys.objects o ON m.object_id = o.object_id
  WHERE type = 'P'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

1.What is MSDB Database in Sql Server?
Main purpose of msdb system database is used to store system activities like jobs, mail, service broker, etc. It is used by SQL Server Agent to schedule jobs, alerts, notification and other features such as Service Broker, Database Mail, SSIS package storage.

The below are the restrictions/operations that cannot be performed on the msdb database
o Changing collation. The default collation is the server collation.
o Dropping the database.
o Dropping the guest user from the database.
o Enabling change data capture.
o Participating in database mirroring.
o Removing the primary filegroup, primary data file, or log file.
o Renaming the database or primary filegroup.
o Setting the database to OFFLINE
o Setting the primary filegroup to READ_ONLY

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

SELECT * from msdb.sys.objects returns all objects under MSDB database....
SELECT * from msdb.sys.tables returns the tables of msdb database

Frequently used tables for SQL Server Agent Jobs are
sysjobsteps
sysjobs
sysjobhistory
sysjobstepslogs
sysjobstepslogs
sysjobschedules

Database Mail related tables under MSDB database are
sysmail_log
sysmail_profile
sysmail_account
sysmail_profileaccount
sysmail_configuration
sysmail_mailitems
sysmail_attachments
sysmail_send_retries

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

3.Normally How to Select all tables in any Database?

SELECT * FROM DatabaseName. sys.Tables

Here, you can replace any database name to display all tables under the database

Alternatives are:

SELECT * FROM DatabaseName.INFORMATION_SCHEMA.TABLES;

SELECT * FROM DatabaseName.sys.objects where type='U'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

5.What is the Meaning .. here SELECT *FROM MSDB..

MSDB.. means that we gonna access/refer objects under default schema of msdb database.

Basically, any object in a database can be referenced by using fully qualified name as below,

DatabaseName.SchemaName.ObjectName

if you want to refer object under default schema of a database

DatabaseName..ObjectName

So MSDB.. means you are going to refer/access/use an object under a default schema of MSDB database..

For example,

SELECT * FROM msdb..backupfile
SELECT * FROM msdb.dbo.backupfile

Both queries are same because the default schema of MSDB

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator