How to get Table structure/Column information using SQL Query

There are different ways to fetch database 'Tables' or 'Views' structure


INFORMATION_SCHEMA views:


Microsoft has introduced 20 different views to fetch helpful database information as part of SQL Server database 2005 and onward. We will use 'columns' view to fetch table structure information


In my openion,  this is the easiest way to fetch table structure information e.g., column name, data type, size, isnullable, table schema etc.


to fetch table structure


select * from INFORMATION_SCHEMA.columns where TABLE_NAME = 'tablename'


or


select column_name, data_type, is_nullable, character_maximum_length
from INFORMATION_SCHEMA.columns where TABLE_NAME = 'tablename'


using database name as prefix



select * 
from database.INFORMATION_SCHEMA.columns
where TABLE_NAME = 'tablename'


-----------------------------------


syscolumns & systypes:

Getting columns and other structural information of a table



select name ,length ,isnullable from syscolumns 
where id = object_id('tablename')



Above query will return name, length and isnullable information for the column but it will not return Data Type name. Data types are not part of the syscolumns table. To fetch them we will have to create an inner join with systypes table


So,
select sc.name, sc.length, sc.isnullable , st.name
from syscolumns sc
inner join systypes st on st.xtype = sc.xtype
where id = object_id('tablename')
order by colid asc

use 'colid' to fetch the correct order of the columns.


using database name as prefix
select * from DatabaseName.sys.syscolumns


--------------------------------------



sp_help:


exec sp_help 'tablename'


Use database name
exec sp_help 'tablename'


-------------------------------------------