Problem solve Get help with specific problems with your technologies, process and projects.

UDF for getting a column list for INSERT statements

Learn about the SQL Server UDF gets a column list for any table within the current database.

If you've ever been in an environment where you're unfamiliar with the database schema, you know how tedious it is to write stored procedures that populate tables using the INSERT statement. This is particularly true if your tables have dozens of columns -- don't laugh, I've seen complex applications that have a few hundred columns per table.

Fortunately you can use the UDF that I offer you in this article to get a column list for any table within the current database. The UDF gets a list of columns from syscolumns system table. Notice that if you simply select column names out of syscolumns the results would be returned in the alphabetical order. However most INSERT statements populate a table according to the order columns appear in that table, rather than using an alphabetic list of columns. For example, the INSERT statement for authors table in pubs database would look as follows:

INSERT authors (
 au_id, 
 au_lname, 
 au_fname, 
 phone, 
 address,   city, 
 state, 
 zip, 
 contract)

SELECT 
 '172-32-1176', 
 'White',
 'Johnson', 
 '408 496-7223', 
 '10932 Bigge Rd.', 
 'Menlo Park', 
 'CA', 
 '94025',
 1 

The order of columns in the table is determined by "colid" column of syscolumns table. Hence the UDF sorts the output by colid. Here is the code:

CREATE FUNCTION udf_get_column_list (@object_name VARCHAR(255))  
RETURNS @t1 TABLE  (column_name VARCHAR(255))
AS  
BEGIN   

DECLARE @t TABLE (id_column INT IDENTITY(1, 1), column_name VARCHAR(255))
INSERT @t (column_name)  
SELECT name + ',' AS column_name FROM syscolumns WHERE id = OBJECT_ID(@object_name)  
ORDER BY colid  

/* get rid of the lat comma */
UPDATE @t 
SET column_name = SUBSTRING(column_name, 1, (LEN(column_name) - 1))
WHERE id_column = (SELECT MAX(id_column) FROM @t)

INSERT @t1
SELECT column_name FROM @t
RETURN
END

Once you create this UDF you can execute it as follows:

SELECT * FROM udf_get_column_list ('sales')

The results:

column_name
stor_id,
ord_num,
ord_date,
qty,
payterms,
title_id

Now you can easily copy the column list and paste it into your INSERT statement. Note that if you have tables owned by database user other than 'dbo' you should specify the object owner along with the table name when calling udf_get_column_list, as follows:

SELECT * FROM udf_get_column_list('owner_name.table_name')

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close