Information Schema regarding a Database table and Database Using sql querys

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



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



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



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...



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



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...