The are three best possible ways to find the "user tables" in SQL Server.
> SELECT NAME
FROM SYS.OBJECTS
WHERE TYPE='U'
> SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
> EXEC SP_TABLES @TABLE_TYPE="'TABLE'"
Comment the one that you use often and the other ways to find the user tables.
5 comments:
How many times, we as a SQL developer or DBA, find ourselves shuffling through objects in Enterprise Manager, or expanding the left pane of Query Analyzer, trying to find a table or view for which we have no clue, except a nearly correct name, and the only way we would know that it is the right object is looking at its meta data or text. Well, it might not be an every day kind of thing, but it does happen from time to time (or perhaps not in an idealistic situation where all databases are well documented and all names follow a well defined naming convention with no exceptions).
A better why to find a SQL Server object, such as a table, a procedure, or a trigger, would be to query the sysobjects system table in the local database (of course, one has to be certain about which database that object is supposed be in).
By the way.. I often use the first script mentioned.
What is the use of SQL Server System Databases?
Although SQL Server's main purpose is to store user database information to support applications, SQL Server 2005 itself uses six system databases to help it perform this function. ated on the SQL Server instance.
A fundamental skill for DBAs is to have a firm understanding of the SQL Server database engine's system databases. It's also useful for database developers to be up on system databases packaged with SQL Server. Here's a rundown of the system databases.
1. Master
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care. For this reason, it is vital to make regular backups of this database.
This database includes information such as system logins, configuration settings, linked servers, and general information regarding the other system and user databases for the instance.
2. Model
Model is essentially a template database used in the creation of any new user database created in the instance. You can place any stored procedures, views, users, etc. in the model database so that when a new database is created, the database will contain the objects you have placed in the model database.
3. Tempdb
As its name implies, tempdb holds temporary objects such as global and local temporary tables and stored procedures.
This database is recreated every time SQL Server starts, and the objects contained in it will be based upon the objects defined in the model database.
4. Distribution
When your SQL Server instance is configured as a distributor for replication, this database is added to your system. By default, the name of the database is distribution, but you can rename it. This database holds history and metadata for snapshot, merge, and transactional replication.
5. Msdb
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
6. Resource
The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
7. ReportServer
Primary database for Reporting Services to store the meta data and object definitions.
Prominent Functionality are Reports security, Job schedules and running jobs,Report notifications, Report execution history.
8. ReportServerTempDB
This DB used as Temporary storage for Reporting Services.
Prominent Functionality are Session information and Cache.
I have found that the best way to learn the underpinnings of SQL Server is to explore how things work in the system databases. As a general rule, it is not recommended to directly query the system tables in SQL Server; however, you can learn a lot about how SQL Server works by exploring the tables in these system databases.
What Master Db will do exactly, i lost my DB(long running scripts or huge data) where i didn't take any backup for that DB.the samething can i get through Master DB to restore the Db(contains structure and data) which i lost.
if this is possible with Master Db then why most of the SQL server users are taking Backup...
The user DB cannot be recovered without a Master data file (MDF) or a backup.As Ansari commented "The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together"
Post a Comment