DROP PROCEDURE IF EXISTS dev.uspGenerateInsertScript  
GO

CREATE PROCEDURE dev.uspGenerateInsertScript
   @pObjectName nvarchar(max)
  ,@pColumNameAs bit = 1
  ,@pIdentityInsert varchar(3) = 'off' -- on / off
  ,@pValuesOrSelect varchar(6) = 'select' -- select / values
AS
BEGIN
  SET NOCOUNT ON;

  IF @pIdentityInsert NOT IN ('on', 'off') OR @pValuesOrSelect NOT IN ('values', 'select') BEGIN
    RAISERROR('@pIdentityInsert értéke ''on'' vagy ''off'' lehet, alapértelmezett az on.  @pValuesOrSelect értéke ''select'' vagy ''values'' lehet, alapértelmezett a select', 16, 1)
    RETURN
  END

  DECLARE 
     @table nvarchar(max)
    ,@schema nvarchar(max)

  IF CHARINDEX('.', @pObjectName) = 0 BEGIN
    SET @table = @pObjectName
    SET @schema = 'dbo'
  END
  ELSE BEGIN
    SET @schema = LEFT(@pObjectName, CHARINDEX('.', @pObjectName) - 1)
    SET @table = SUBSTRING(@pObjectName, CHARINDEX('.', @pObjectName) + 1, 1000)
  END

  DECLARE @tableVariable TABLE (
     Id int identity(1,1) primary key
    ,Ertek nvarchar(max)
  )

  IF @pIdentityInsert = 'on' BEGIN
    INSERT INTO @tableVariable (Ertek)
    SELECT 'SET IDENTITY_INSERT ' + @pObjectName + ' ON;'
  END 

  INSERT INTO @tableVariable (Ertek)
  SELECT 'INSERT INTO ' + @pObjectName + ' ('  

  INSERT INTO @tableVariable (Ertek)
  SELECT
  IIF(ROW_NUMBER() OVER(ORDER BY c.column_id) = 1 , '   ' , '  ,') + c.name
  FROM sys.columns c
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
  WHERE s.name = @schema
    AND t.name = @table
    AND (@pIdentityInsert = 'on' OR c.is_identity = 0)
  ORDER BY c.column_id

  INSERT INTO @tableVariable (Ertek)
  SELECT IIF(@pValuesOrSelect = 'select' ,') SELECT', ') VALUES (')

  INSERT INTO @tableVariable (Ertek)
  SELECT 
     IIF(ROW_NUMBER() OVER(ORDER BY c.column_id) = 1 , '   ' , '  ,') 
     + IIF(@pColumNameAs = 1, c.name, ISNULL(IIF(ty.name IN ('int', 'float', 'numeric'), REPLACE(REPLACE(d.definition, '(', ''), ')', ''), REPLACE(SUBSTRING(d.definition, 2, LEN(d.definition) - 2), 'getdate', 'GETDATE')), 'NULL'))
     + IIF(@pValuesOrSelect = 'select' ,' AS ', ' -- ')   
     + c.name  
     + ' -- '  
     + IIF(c.is_nullable = 1, '', '* ')  
     + ty.name  
     + CASE 
        WHEN c.max_length = -1 THEN '(max)'
        WHEN ty.name IN ('nchar', 'nvarchar') THEN '(' + CAST(c.max_length / 2 AS nvarchar(5)) + ')'   
        WHEN ty.name IN ('char', 'varchar') THEN '(' + CAST(c.max_length AS nvarchar(5)) + ')'
        ELSE ''
     END 
  FROM sys.columns c
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
    LEFT JOIN sys.default_constraints d ON d.object_id = c.default_object_id
  WHERE s.name = @schema
    AND t.name = @table
    AND (@pIdentityInsert = 'on' OR c.is_identity = 0)
  ORDER BY c.column_id

  INSERT INTO @tableVariable (Ertek)
  SELECT IIF(@pValuesOrSelect = 'select' ,'FROM ', ');')

  IF @pIdentityInsert = 'on' BEGIN
    INSERT INTO @tableVariable (Ertek)
    SELECT 'SET IDENTITY_INSERT ' + @pObjectName + ' OFF;'
  END 

  SELECT Ertek 
  FROM @tableVariable
  ORDER BY id
END
GO