To Get all columns information of a table we can use following command
SELECT st.name, sc.name,sc.is_identity AS IdentityColumn,
sc.is_nullable AS NullableColumn,
sc.max_length as colmaxlength,
sty.name as columnType
FROM sys.columns sc
INNER JOIN sys.tables st ON st.object_id = sc.object_id
INNER JOIN systypes sty on sty.xtype = sc.system_type_id
ORDER By st.name,sc.name
Or we can use following statement
To determine table detail like column name & data types for each individual table ,Index, primary key, foreign keys etc, sp_help is one of the best way.
Syntax of sp_help is very simple as shown below
Instead of writing sp_help tablename you can get same information by highlighting table and press ALT+ F1
Note: if you just right sp_help and run it then all the objects of database will be listed down as a result.
To determine size of table in a database or size of database, we have simple syntax in SQL Server which is SP_SPACEUSED.
Syntax is simple enough as shown below
1) If we want to determine size of the database then we have to write below statement and execute
2) Now to determine size of particular table we need to write following syntax
3) Now to determine size of each table in a database we need to write following syntax
SP_MSFOREACHTABLE ‘SP_SPACEUSED ’
4) When we are running sp_spaceused it may possible the size is not updated so to make sure size is updated we need to write following command
SP_SPACEUSED @updateUsage =N’True’
In the output of each statements we are seeing reserved, data,Index_Size & unused columns.
1) reserved column shows total space reserved by object.
2) data column shows total space used by the data.
3) index_Size shows total space used by index in object.
4)unused shows total space reserved by object but not yet used.
For more information you can visit following link