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

Posted by Jayakumars under Sql Server on 4/15/2015 | Points: 10 | Views : 662 | Status : [Member] [MVP] | Replies : 5
Hi

1.What is MSDB Database in Sql Server?
2.Why use this then how to find behind all tables in this database?
3.Normally How to Select all tables in any Database?
4.How to get all Procedure particular Database?
5.What is the Meaning .. here SELECT *FROM MSDB..
why we need given this.

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 4/15/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Bandi on: 4/15/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 4/15/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 4/15/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 4/15/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response