Auto Generate SQL Variable Syntax for Table Column Names

Problem

I work with SQL tables with many programming languages. Often, I need to code variables related to the table column names. Is there any easier way that doesn’t involve excessive typing?

Solution

As a Developer, automation is the most efficient way to save time when dealing with repetitive tasks. To illustrate, the code below produces a table about car details:

CREATE TABLE [dbo].[Automobile](
   [EventId] [int] IDENTITY(1,1) NOT NULL,
   [AutomobileId] [nvarchar](50) NULL,
   [Day] [date] NOT NULL,
   [Place] [nvarchar](50) NULL,
   [Amount] [money] NULL,
   [Price] [money] NULL,
   [Odometer] [int] NULL,
   [Comments] [nvarchar](50) NULL,
   [Mileage] [int] NULL,
   [Consumption] [money] NULL,
   [FuelEfficiency]  AS ([Mileage]/[Consumption]),
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Automobile] PRIMARY KEY CLUSTERED 
(
   [EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

In the Project Explorer of SQL Server Management Studio, right-click on the table name. Choose Script Table as > UPDATE To/ INSERT To > New Query Editor Window. The following will appear:

Next, we need to fill out the values or set portions of these queries. There is a way to speed up this process using the [INFORMATION_SCHEMA].[COLUMNS] table.

Creation of the Store Procedure

-- ==================================================
-- Author:      Sebastião Pereira - MSSQLTips
-- Create date: 20240723
-- Description: Columns Tool based on Template
-- ==================================================
CREATE PROCEDURE [dbo].[uspTableToModel] 
         (@Schema varchar(50)
         ,@Table varchar(50)
         ,@Template nvarchar(500)
         ,@ShowAsList bit
         ,@ConvertNet bit)
WITH EXECUTE AS CALLER
AS
BEGIN
   SET NOCOUNT ON;
   BEGIN TRY
 
      /* Convention:
         Symbol Column
         {cn} [COLUMN_NAME] 
         {dt} [DATA_TYPE]
      */
 
      IF @ShowAsList = 0 
         SELECT    REPLACE(REPLACE(@Template
                    ,'{cn}',[COLUMN_NAME])
                     ,'{dt}',[dbo].[ufnReturnSqlDataType] ([DATA_TYPE],[CHARACTER_MAXIMUM_LENGTH],@ConvertNet)) AS [Result]
            FROM  [INFORMATION_SCHEMA].[COLUMNS]
            WHERE  [TABLE_SCHEMA] = @Schema AND
                [TABLE_NAME] = @Table;
      ELSE
         SELECT     STRING_AGG(REPLACE(REPLACE(@Template
                     ,'{cn}',[COLUMN_NAME])
                     ,'{dt}',[dbo].[ufnReturnSqlDataType] ([DATA_TYPE],[CHARACTER_MAXIMUM_LENGTH],@ConvertNet)), ',') 
                  WITHIN GROUP (ORDER BY [ORDINAL_POSITION]) AS [Result]
            FROM  [INFORMATION_SCHEMA].[COLUMNS]
            WHERE  [TABLE_SCHEMA] = @Schema AND
                [TABLE_NAME] = @Table;
 
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         BEGIN
            ROLLBACK TRANSACTION;
         END
         
      -- Print error information. 
      PRINT  'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
         ', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
         ', State: ' + CONVERT(varchar(5), ERROR_STATE()) + 
         ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') + 
         ', Line: ' + CONVERT(varchar(5), ERROR_LINE()) + 
         ', User: ' + CONVERT(varchar(5), GETDATE());
      PRINT ERROR_MESSAGE();
   END CATCH; 
END
GO

Creation of the Function

-- ==================================================
-- Author:      Sebastião Pereira - MSSQLTips
-- Create date: 20240723
-- Description: Return SQL Data type
-- ==================================================
CREATE FUNCTION [dbo].[ufnReturnSqlDataType] 
            (@DataType nvarchar(50)
            ,@MaxLength nvarchar(50)
            ,@ConvertToDotNet bit)
RETURNS nvarchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
   DECLARE @Result nvarchar(MAX);
 
   IF @ConvertToDotNet = 1
      BEGIN
         SET @Result =
            CASE 
               WHEN @DataType IN ('bigint') THEN 'long'
               WHEN @DataType IN ('int') THEN 'int'
               WHEN @DataType IN ('smallint') THEN 'short'
               WHEN @DataType IN ('tinyint') THEN 'byte'
               WHEN @DataType IN ('bit') THEN 'boolean'
               WHEN @DataType IN ('decimal','numeric','money','smallmoney') THEN 'decimal'
               WHEN @DataType IN ('float') THEN 'double'
               WHEN @DataType IN ('real') THEN 'single'
               WHEN @DataType IN ('char','nchar','varchar','nvarchar','text','ntext') THEN 'string'
               WHEN @DataType IN ('date','datetime','datetime2','smalldatetime') THEN 'DateTime'
               WHEN @DataType IN ('time') THEN 'TimeSpan'
               WHEN @DataType IN ('datetimeoffset') THEN 'DateTimeOffset'
               WHEN @DataType IN ('binary','varbinary','image','timestamp','rowversion') THEN 'byte[]'
               WHEN @DataType IN ('uniqueidentifier') THEN 'guid'
               WHEN @DataType IN ('xml') THEN 'XmlDocument'
               WHEN @DataType IN ('sql_variant') THEN 'Object'
            END;
      END
   ELSE
      SET @Result = CONCAT(@DataType,'(' + CONVERT(varchar(10),@MaxLength) + ')');
 
   RETURN @Result;
END
GO

Filling Out Data

Our stored procedure has five variables:

1. @Schema: the name of the schema of the table that we want to query.
2. @Table: the table name.
3. @Template: what we want to retrieve using as a convention {cn} for Column name and {dt} for Data type.
4. @ShowAsList: if false, will return the data in rows, and if true, will return as a string.
5. @ConvertNet: if false, will return SQL data types, and if true, will return .NET data types.

Templates examples

For the Automobile table running with these templates will generate the respective results for the column PLACE:

Convert to Net example

Insert Case

To insert the values clause, we need to pass them with variables. The trick is to create a template like N’,@{cn}’, execute the store procedure, select the result, and copy and paste the VALUES clause of the query. Remember to remove the comma from the first element and refine the formatting of your code.

Update Case

Now, to insert the set elements, we will create the template N’,[{cn}] = @{cn}’, execute the stored procedure, select the result, and copy and paste the SET clause of the query. Remember to remove the comma from the first element and refine the formatting of your code.

Other Useful Cases

Developers often encounter situations where they need to create repetitive statements related to SQL table columns. Here are a few examples.

Note: This is only a sample of example situations. They are too numerous to include in this article.

Creating a Class in C#

Creating a Class in VB

Initializing Variables in VUE

List of All Columns with their Data Type and Column Name Separated by Comma

List of Column Names as Variables

List of All Columns with their Data Type in .NET and Column Name

Statement in SQL to Check if the Column is Different than its Variable, Then Change It; Otherwise Set as Null

List of Columns Names Surrounded By Quotes and Double Quotes

The result value of this stored procedure can be used in any programming task related to SQL table columns. Simply copy and paste the output into your code, then fix the formatting and make any necessary adjustments.

Next Steps

• Pay attention to details like removing the comma in the first element, aligning the items, and using only the values of the needed columns.
• If you need to improve the stored procedure, you can use the column [IS_NULLABLE] to check when it is necessary to include the term REQUIRED in the result set.

Sebastiao Pereira

Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry.  Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.

• MSSQLTips Awards
  • Author of the Year – 2025
  • Trendsetter (25+ tips) – 2025
  • Rookie of the Year – 2024