In SQL Server, we have two methods to get the information related to the columns. These two methods are:
- Using INFORMATION_SCHEMA.COLUMNS
- Using sys.columns
Both provide the information related to the existing columns in the current database. Let us check:
INFORMATION_SCHEMA.COLUMNS
sys.columns
We can see that we get the information about the columns using InformationSchema as well as the system views. But, there are some differences b/w both the methods, as described below:
- INFORMATION_SCHEMA.COLUMNS method only provides the physical information related to all the columns like Table name, column name, data type and other information data types and domain related information.
- INFORMATION_SCHEMA.COLUMNS method doesn't provide information related to the schemas of the columns like column data precision and max_length.
- INFORMATION_SCHEMA.COLUMNS method is only related to tables and views while sys.columns method also contains column information about Table-valued assembly functions (FT), Internal tables (IT), System tables (S) and Table-valued SQL functions (TF).
So, use INFORMATION_SCHEMA.COLUMNS when you want to get columns name, table name or some other general information. But, if you are willing to get schema related information or some other information, then prefer the sys.columns method over INFORMATION_SCHEMA.COLUMNS.