Dex
Dex

Reputation: 23

How/Where is table structure(not data) stored in SQL server?

I know that the data in SQL Server is stored in Data pages, But I don't know where the table structure is stored. I came across a statement about TRUNCATE as

"TRUNCATE removes the data by deallocating the data pages.TRUNCATE removes all rows from a table, but the table structure and columns remains"

This made me realize that, table structure, column information is stored outside pages(or Data pages in particular). SO, How/Where is table structure(not data) is stored in SQL server ?

Thank You.

Upvotes: 1

Views: 641

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

There are system tables that store all of the metadata about the database. These tables are not directly queryable (except when using the DAC) but there are numerous views and functions built atop these tables. These are referred to as the Catalog Views.

So, for instance, there is the sys.columns view which describes each column in the database. It's a view built atop the syscolpars table, which is one of the system tables mentioned above that you cannot directly query.

There are also the INFORMATION_SCHEMA views which hespi mentions. These are meant to be a "standard" way of accessing metadata supported by all SQL database systems. Unfortunately, support for them is not 100%, and because they're meant to be cross-platform, they do not tend to reveal advanced features that are product specific.

Upvotes: 1

You can access SQL server metadata on INFORMATION_SCHEMA. Following find the most useful views and its content:

  • INFORMATION_SCHEMA.TABLES: Contains information about the schemas, tables and views in the server.
  • INFORMATION_SCHEMA.COLUMNS: Full information about the table columns like data type, if it's nullable...
  • INFORMATION_SCHEMA.VIEWS: Containing information about the views and the code for creating them again.
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE: Information about foreign keys, unique keys, primary keys...

To use them, simply query them as they are data views: SELECT * FROM INFORMATION_SCHEMA.TABLES

For a full reference go to MSDN: https://msdn.microsoft.com/en-us/library/ms186778.aspx

Upvotes: 2

user4622594
user4622594

Reputation:

A SQL Server Database consists of 2 Files (usually):

  • Master Data File (*.mdf)
  • Transaction Log File (*.ldf)

)The Master Data File contains: Schema and Data Information

)The Transaction Log Files contains Log Information for Actions in your DB

If you run select * from sys.database_files in your DB it will show you the filenames, location, size, etc..

Upvotes: 1

Related Questions