Auto Query Generator In MSSQL Server

Before explaining the script, I want to share the reason to write this script and how it is helping my peers. We have code standard on the database side. Below points are standards.

• Need to maintain a separate stored procedure to every table
• Don’t use * in the query instead specify the column
• Use the correct data type and size of a column
• Every parameter should be nullable in a stored procedure.

CREATE PROC [dbo].[USP_QueryCreationSupport] (@table_Name varchar(100))
AS
BEGIN
    DECLARE @InserCols NVARCHAR(max)
    DECLARE @Inserparam NVARCHAR(max)
    DECLARE @Insertquery NVARCHAR(max)
    DECLARE @Selectquery NVARCHAR(max)
    DECLARE @Update NVARCHAR(max)
    DECLARE @DeleteQuery NVARCHAR(max)

    SELECT '@' + c.NAME + Space(1) + Case cast(t.Name as nvarchar(40))
                                         WHEN 'nvarchar' THEN
                                             t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
                                         WHEN 'varchar' THEN
                                             t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
                                         WHEN 'char' THEN
                                             t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
                                         WHEN 'decimal' THEN
                                             t.NAME + '(18,2)'
                                         ELSE
                                             t.name
                                     end + ' = null,' AS Params
FROM sys.columns c
        inner join sys.types t
            ON c.user_type_id = t.user_type_id
    WHERE c.object_id = object_id(@table_Name)

    SET @InserCols =
    (
        select distinct
            (
                select sc.NAME + ','
                FROM sys.tables st
                    inner join sys.columns sc
                        ON st.object_id = sc.object_id
                WHERE st.NAME = @table_Name
                for xml path(''), type
            ).value('.', 'NVARCHAR(MAX)')
    )
    SELECT @InserCols = LEFT(@InserCols, Len(@InserCols) - 1)

    SET @Inserparam =
    (
        select distinct
            (
                select '@' + sc.NAME + ','
                FROM sys.tables st
                    inner join sys.columns sc
                        ON st.object_id = sc.object_id
                WHERE st.NAME = @table_Name
                for xml path(''), type
            ).value('.', 'NVARCHAR(MAX)')
    )


    SELECT @Inserparam = LEFT(@Inserparam, Len(@Inserparam) - 1)

    SET @Insertquery = 'insert into ' + @table_Name + ' (' + @InserCols + ')' + ' values ' + ' (' + @Inserparam + ')'

    SELECT @Insertquery AS InsertQuery


    SET @Update =
    (
        select distinct
            (
                select sc.NAME + '= @' + sc.NAME + ','
                FROM sys.tables st
                    inner join sys.columns sc
                        ON st.object_id = sc.object_id
                WHERE st.NAME = @table_Name
                for xml path(''), type
            ).value('.', 'NVARCHAR(MAX)')
    )

    SELECT @Update = LEFT(@Update, Len(@Update) - 1)

    SET @Update = 'Update ' + @table_Name + ' set ' + @Update

    SELECT @Update AS UpdateQuery

    SET @Selectquery = 'select ' + @InserCols + ' from ' + @table_Name

    SELECT @Selectquery AS SelectQuery
END

--Execute the stored procedure and pass your table name as a parameter.
EXECUTE dbo.[USP_QueryCreationSupport] @table_Name = 'settings'

SQL SERVER – Custom TRIM Function

ALTER FUNCTION [dbo].[fn_Trim](@String NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
    RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),'[]'),CHAR(13),'[]'),char(9),'[]'),CHAR(32),'[]'),'][',''),'[]',CHAR(32))));
END

Get All Primary Key In SQL

-- Let's get the columns of the Primary key into a CTE 
;WITH mycte 
     AS (SELECT Schema_name(o.schema_id)            AS 'Schema', 
                Object_name(i2.object_id)           AS 'TableName', 
                Stuff((SELECT ',' + Col_name(ic.object_id, ic.column_id) 
                       FROM   sys.indexes i1 
                              INNER JOIN sys.index_columns ic 
                                      ON i1.object_id = ic.object_id 
                                         AND i1.index_id = ic.index_id 
                       WHERE  i1.is_primary_key = 1 
                              AND i1.object_id = i2.object_id 
                              AND i1.index_id = i2.index_id 
                       FOR xml path('')), 1, 1, '') AS PK 
         FROM   sys.indexes i2 
                INNER JOIN sys.objects o 
                        ON i2.object_id = o.object_id 
         WHERE  i2.is_primary_key = 1 
                AND o.type_desc = 'USER_TABLE') 

-- Use this select to get the count, join to the CTE and get the column list 

SELECT Schema_name(o.schema_id)                    AS 'Schema', 
       Object_name(i.object_id)                    AS 'TableName', 
       Count(Col_name(ic.object_id, ic.column_id)) AS 'Primary_Key_Column_Count' 
       , 
       mycte.pk                                    AS 
       'Primary_Key_Columns' 
FROM   sys.indexes i 
       INNER JOIN sys.index_columns ic 
               ON i.object_id = ic.object_id 
                  AND i.index_id = ic.index_id 
       INNER JOIN sys.objects o 
               ON i.object_id = o.object_id 
       INNER JOIN mycte 
               ON mycte.tablename = Object_name(i.object_id) 
WHERE  i.is_primary_key = 1 
       AND o.type_desc = 'USER_TABLE' 
GROUP  BY Schema_name(o.schema_id), 
          Object_name(i.object_id), 
          mycte.pk   
ORDER  BY 'TableName' ASC