Information Schema regarding a Database table and Database Using sql querys


In this Article i will discuss how to get structure(Schema) of a table through sql query that Schema Information includes Columns,Datatypes,size/Precision relationships and structure of a Database includes size ,owner ,status , compatibility level , constraints and Views that exists in the Database with code snippets and real time images.

In sql-server we can query to get the information for schema of table or database by giving table name in where condition.Using this query we can able to know the column Names that persists in the table ,datatypes and relationship with other columns that exists in another tables ,Ordinal positions of Columns in the table and also we can know the size of the database and owner of the database its created date and path of the the database file to create for backups or restores of the database.In this Article i will discuss how many ways we can query to get schema of a Database table or table structure or Database.

Query : 1

Informationschema

The Alternative to the above Query is like this....
Query : 2

Alternate above query

If you carefully observe the first query and second query the Datatype column in the second one is in Numbers or in integers(4,-9 e.t.c...) where as the first one is in Names.

The Bottom line is that sqlserver database internally do maintain some Numbers for each datatype for integer it is 4 and some other it is another Number...

Query :3

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Schema of a table

The above query will retrive all Tablename,columnname ,datatypes and size for a Catalog/Database..

Query :4

sp_help tablename

Information of a Particular table...

Information of a Particular table

The above query will get the information of a table i.e column name ,data type ,length,Nullable and relationship of a particular column in another table...


Query 5

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

With the above query we can check what are the constraints that are available in the table columns for ex if we create a constraint for a particular column i.e the column value should not be greater than 10 and not less than 0 it will display that when you use the above query.

Query 6

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW

With the above query we can know what are the views that exists in the Database that will display as a result set or Out put.


Query 7

sp_helpdb Hostingadmin

Databasesizepath

In the above query we see that the size of the database the growth of the database,the usage of the database and more importantly the path of the database where it is more useful in restore and backups of a database...


Attachments

Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 74 articles authored by srirama

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: