95 lines
3.2 KiB
Transact-SQL
95 lines
3.2 KiB
Transact-SQL
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
|