How/Where is table structure(not data) stored in SQL server?
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.
How to fetch data stored by SQL?
No, you cannot import SQL Server data files into SQLite, as those are totally unrelated products and store data in different formats.
You can, however, export your database as SQL and import that into SQLite. Note, however, that both databases use slightly different SQL dialects, so you may have to correct generated SQL files by hand before thay can be imported into SQLite.
How are varchar values stored in a SQL Server database?
Completely pointless restriction as far as I can see. Assuming standard FixedVar
format (as opposed to the formats used with row/page compression or sparse columns) and assuming you are talking about varchar(1-8000)
columns
All varchar
data is stored at the end of the row in a variable length section (or in offrow pages if it can't fit in row). The amount of space it consumes in that section (and whether or not it ends up off row) is entirely dependant upon the length of the actual data not the column declaration.
SQL Server will use the length declared in the column declaration when allocating memory (e.g. for sort
operations). The assumption it makes in that instance is that varchar
columns will be filled to 50% of their declared size on average so this might be a better thing to look at when choosing a size.
Where are views stored in SQL Server
The pedantic answer to your question is... only Microsoft knows exactly where view metadata is physically stored. In the move from SQL 2000 to SQL 2005 (on which 2008 is based) MS got rid of direct access to system tables where views used to be literally stored (dbo.sysviews and dbo.syscomments) and added a layer of abstraction (via the hidden resources database) which means you can only access meta data about views via catalog views. INFORMATION_SCHEMA is an ANSI compliant set of catalog views. While marginally useful for their relative portability between versions, often more information is available from the sql 2008 catalog views - in this case sys.views and sys.sql_modules
Be aware that views can be created with the ENCRYPTION option set which encrypts the sys.comments record(s) that contain the SQL definition of the view. But if not encrypted, then sp_helptext [MyView] will give you a quick look at the definition.
edited as per 1st comment below, to replace "sys.comments" with "sys.sql_modules"
Related Topics
Select Where Count of One Field Is Greater Than One
How to Export Data with Oracle SQL Developer
Return Bit Value as 1/0 and Not True/False in SQL Server
Prevent Insert If Condition Is Met
Sql: Search for a String in Every Varchar Column in a Database
How to Perform a Bitwise Group Function
How to Create an Alias of Database in SQL Server
Pairwise Array Sum Aggregate Function
Postgresql: Fill Null Values in Timeserie Query with Previous Value
How to Write a Function in the H2 Database Without Using Java
Select Columnvalue If the Column Exists Otherwise Null
How Is Data Stored in SQL Server
How to Concatenate Strings in Entity Framework Query
Equals(=) VS. Like for Date Data Type
How to Use Non-Aggregate Columns with Group By