Create a copy of a database without any data

Create dacpac

install:

Microsoft® SQL Server® Data-Tier Application Framework (June 30 2016)

https://www.microsoft.com/en-us/download/details.aspx?id=53013

C:\Program Files\Microsoft SQL Server\130\DAC\bin>sqlpackage /Action:Extract /SourceDatabaseName:”games” /SourceServerName:SERVERNAME /TargetFile:”C:\dac\games.dacpac”

create new database from dacpac

within management studio

right click databases folder
deploy data tier application
follow the GUI instructions
configure database

process a table of commands via a stored proc

CREATE TYPE command_list AS TABLE (id int,cmd varchar(MAX))

–SELECT * FROM sys.types WHERE is_user_defined = 1

GO

CREATE PROC dba.usp_process_command_list

@input_list command_list readonly
AS

–jlester 20180329 – takes a list of commands and processes them
–input table has 2 fields id – an int starting from 1 and incrementing by 1 for each command
–cmd – is the command to be processed
— here is a sample block of code:

/*

DECLARE @x command_list

INSERT INTO @x
SELECT 1 ,’select 1′
UNION ALL
SELECT 2 ,’select getdate()’

EXEC dba.usp_process_command_list @input_list = @x

*/

DECLARE @command_list TABLE
(id int,cmd varchar(MAX))

INSERT @command_list
SELECT * from @input_list

DECLARE @maxid INT = (SELECT MAX(id) FROM @command_list) –find the last command to be processed
DECLARE @curid INT = 1 –start from command 1
DECLARE @curcmd VARCHAR(MAX)

–PRINT @maxid

while @curid<=@maxid –loop through the list 1 record at a time
BEGIN
–PRINT @curid
SET @curcmd = (SELECT cmd FROM @command_list WHERE id =@curid) –fetch the command for the current id

–print @curcmd
exec (@curcmd)

SET @curid += @curid –increment the current id by 1
–PRINT @curid
END

–SELECT * FROM @command_list

GO

DECLARE @x command_list

INSERT INTO @x
SELECT 1 ,'select 1'
UNION ALL
SELECT 2 ,'select getdate()'

EXEC dba.usp_process_command_list @input_list = @x

GO