Reputation: 23
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
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
Reputation: 900
You can access SQL server metadata on INFORMATION_SCHEMA. Following find the most useful views and its content:
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
Reputation:
A SQL Server Database consists of 2 Files (usually):
)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