Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Retrieve Column Information in SQL Server – Querychat

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

Examples

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc