In the article, we learn how to retrieve column information of objects in SQL Server using a system stored procedure named sp_columns. Examples of using this stored procedure to get the column information of tables and views are provided.
SQL Server provides the sp_columns system stored procedure that is used to describe the structure of a table or to return column information for specified objects. This information can be queried in the current database environment through Transact-SQL. We can use the sp_columns stored procedure on objects that only have columns such as tables, views, or other objects.
The sp_columns syntax
To run this system’s stored procedure, permissions are required on the schema, SELECT and VIEW DEFINITION. The syntax is as follows:
EXEC sp_columns @table_name = N'ObjectName' , [ @table_owner = N'OwnerName' , ] [ @table_qualifier = N'QualifierName' , ] [ @column_name = N'ColumnName' , ] [ @ODBCVer = ODBC_Version ]
- [ObjectName] contains the name of objects such as a table, view, or another object that has columns. It is the only required argument.
- [OwnerName] is optional and contains the name of the owner object.
- [QualifierName] is optional and contains the name of the object qualifier.
- [ColumnName] is optional and contains a single column name.
- [ODBC_Version] is optional and contains the version of ODBC that is being used
Our example playground
For our demonstration purpose, we create a country table in the SampleDB database. And also a view named v_country. The table consists of the ID, and name columns.
Create a database and a table
See the following syntax that is used to create them and fill them:
CREATE DATABASE SampleDB; GO USE SampleDB; GO CREATE TABLE country (ID INT, name VARCHAR(50));
We can see the country table created through the SSMS Object Explorer and the result looks like this:
Figure 1. An example of the country table
Insert records into the table
Let’s add some sample records into the country table using the syntax below:
INSERT INTO country VALUES (1, 'United States'); INSERT INTO country VALUES (2, 'Brazil'); INSERT INTO country VALUES (3, 'Mexico');
The result will appear:
Figure 2. Records in the country table
Create a View
We create a view named v_country. It contains a SELECT statement to retrieve IDs and names of countries in the country table that has ID over 1. See the following query through T-SQL:
CREATE VIEW dbo.v_country AS SELECT ID, name FROM country WHERE ID > 1 GO
Let’s look at several examples below on how to use the sp_column to retrieve column information of the country table and v_country view that we have created in the previous steps.
Example #1. Return all columns info by the table name
In this first example, we use the SampleDB database and execute the sp_columns stored procedure with the table name ‘country’ as an argument. The syntax is as follows:
USE SampleDB; EXEC sp_columns @table_name = N'country';
Or to make more concise, it can also be without @table_name like this:
EXEC sp_columns N'country';
Here is the result that only display information with a few columns:
Figure 3. Executing the sp_columns with a specific table name
Example #2 Return info for a specific column
The following syntax is used to retrieve information on the ID column of the country table.
EXEC sp_columns @table_name = N'country', @column_name = N'ID';
The result only displays information with a few columns:
Figure 4. Executing the sp_columns with table and column name
Example #3. Return info for a specific column of a view
In this case, the syntax is the same as the previous example (Example #2), regardless of the object type. The following query is used to get the name column information for the v_country view in the SampleDB database:
EXEC sp_columns @table_name = N'country', @column_name = N'name';
The result will appear only information with a few columns:
Figure 5. Executing the sp_columns with view and column name
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.