A script that inserts exactly one row in all tables in database

DataFiller.sql

--------------------------------------------------------
-- Insert one row into all tables within the database --
--------------------------------------------------------
-- Works On My Machine (TM) when tested with the AdventureWorks 2019 backup

use AdventureWorks2019;
set nocount on;

go

drop procedure if exists #ToggleCheckConstraints;
go
create or alter procedure #ToggleCheckConstraints
	@Enabled bit
as

declare
	@TableSchema sysname,
	@TableName sysname,
	@CheckConstraintName sysname,
	@Query nvarchar(2000),
	@Print nvarchar(2000);

declare curs cursor local fast_forward
for
select object_schema_name(t.object_id) as TableSchema, t.name as TableName, ck.name as CheckConstraintName
from sys.check_constraints ck
inner join sys.tables t on ck.parent_object_id = t.object_id
where
	t.is_ms_shipped = 0
	and ck.is_ms_shipped = 0
	and ck.is_disabled = (case when @Enabled = 1 then 1 else 0 end);

open curs;
fetch next from curs into @TableSchema, @TableName, @CheckConstraintName;

while @@FETCH_STATUS = 0
begin
	select @Query =
		case
			when @Enabled = 1 then
				concat(
					N'alter table ', quotename(@TableSchema), N'.', quotename(@TableName),
					N' with check check constraint ', quotename(@CheckConstraintName), N';'
				)
			else
				concat(
					N'alter table ', quotename(@TableSchema), N'.', quotename(@TableName),
					N' nocheck constraint ', quotename(@CheckConstraintName), N';'
				)
		end;

	select @Print = concat(N'Running query: ', @Query);
	print @Print;

	exec sp_executesql @stmt = @Query;

	fetch next from curs into @TableSchema, @TableName, @CheckConstraintName;
end

return 0;

go



drop procedure if exists #ToggleForeignKeys;
go
create or alter procedure #ToggleForeignKeys
	@Enabled bit
as

declare
	@TableSchema sysname,
	@TableName sysname,
	@FkName sysname,
	@Query nvarchar(2000),
	@Print nvarchar(2000);

declare curs cursor local fast_forward
for
select object_schema_name(t.object_id) as TableSchema, t.name as TableName, fk.name as FkName
from sys.foreign_keys fk
inner join sys.tables t on fk.parent_object_id = t.object_id
where
	t.is_ms_shipped = 0
	and fk.is_ms_shipped = 0
	and fk.is_disabled = (case when @Enabled = 1 then 1 else 0 end);

open curs;
fetch next from curs into @TableSchema, @TableName, @FkName;

while @@FETCH_STATUS = 0
begin
	select @Query =
		case
			when @Enabled = 1 then
				concat(
					N'alter table ', quotename(@TableSchema), N'.', quotename(@TableName),
					N' with check check constraint ', quotename(@FkName), N';'
				)
			else
				concat(
					N'alter table ', quotename(@TableSchema), N'.', quotename(@TableName),
					N' nocheck constraint ', quotename(@FkName), N';'
				)
		end;

	select @Print = concat(N'Running query: ', @Query);
	print @Print;

	exec sp_executesql @stmt = @Query;

	fetch next from curs into @TableSchema, @TableName, @FkName;
end

return 0;
go



drop procedure if exists #DeleteTables;
go
create or alter procedure #DeleteTables
as

begin try
	exec sp_MSforeachtable N'delete ?;';
end try
begin catch
	if ERROR_NUMBER() = 1934
	begin
		exec sp_MSforeachtable N'set quoted_identifier on; delete ?;';
	end
end catch

return 0;

go



drop procedure if exists #ToggleTriggers;
go
create or alter procedure #ToggleTriggers
	@Enabled bit
as

declare
	@TableSchema sysname,
	@TableName sysname,
	@TriggerName sysname,
	@Query nvarchar(2000),
	@Print nvarchar(2000);

if @Enabled = 1
begin
	print N'Running query: enable trigger all on database;';
	enable trigger all on database;
end
else
begin
	print N'Running query: disable trigger all on database;';
	disable trigger all on database;
end

declare curs cursor local fast_forward
for
select object_schema_name(t.object_id) as TableSchema, t.name as TableName, tr.name as TriggerName
from sys.triggers tr
inner join sys.tables t on tr.parent_id = t.object_id
where
	t.is_ms_shipped = 0
	and tr.is_disabled = (case when @Enabled = 1 then 1 else 0 end);

open curs;
fetch next from curs into @TableSchema, @TableName, @TriggerName;

while @@FETCH_STATUS = 0
begin
	select @Query =
		case
			when @Enabled = 1 then
				concat(
					N'enable trigger ', quotename(@TriggerName),
					N' on ', quotename(@TableSchema), N'.', quotename(@TableName), N';'
				)
			else
				concat(
					N'disable trigger ', quotename(@TriggerName),
					N' on ', quotename(@TableSchema), N'.', quotename(@TableName), N';'
				)
		end;

	select @Print = concat(N'Running query: ', @Query);
	print @Print;

	exec sp_executesql @stmt = @Query;

	fetch next from curs into @TableSchema, @TableName, @TriggerName;
end

return 0;

go



drop procedure if exists #InsertRows;
go
create or alter procedure #InsertRows
as

declare
	@TableObjectId int,
	@TableSchema sysname,
	@TableName sysname,
	@HasIdentityColumn bit,
	@Query nvarchar(2000),
	@Print nvarchar(2000);


declare curs cursor local fast_forward
for
select
	t.object_id as TableObjectId,
	object_schema_name(t.object_id) as TableSchema,
	t.name as TableName,
	cast(
		coalesce(
			(
				select 1
				from sys.columns
				where object_id = t.object_id
					and is_identity = 1
			), 0
		)
	as bit) as HasIdentityColumn
from sys.tables t
where t.is_ms_shipped = 0;

open curs;
fetch next from curs into @TableObjectId, @TableSchema, @TableName, @HasIdentityColumn;

while @@FETCH_STATUS = 0
begin
	select @Print = concat(N'Generating insert query for: ', quotename(@TableSchema), N'.', quotename(@TableName));
	print @Print;

	select @Query = N'';

	if (@HasIdentityColumn = 1)
	begin
		select @Query +=
			concat(
				N'set identity_insert ',
				quotename(@TableSchema), N'.', quotename(@TableName),
				N' on;
'
			);
	end

	select @Query +=
		concat(
			N'insert into ', quotename(@TableSchema), N'.', quotename(@TableName), N' ',
			N'(
',
			(
				select string_agg(quotename(c.name), N',
')
				from sys.columns c
				where c.object_id = @TableObjectId and c.user_type_id != 189 and c.is_computed = 0 and c.is_nullable = 0
			)
			, N')
',
			N'values (',
			(
				select
					string_agg(
						concat(
							(case when t.name not in ('binary', 'image', 'varbinary') then N'''' else N'' end),
							case
								when t.name in ('bigint', 'int', 'smallint', 'tinyint') then N'0'
								when t.name in ('binary', 'varbinary') then N'0x0'
								when t.name = 'bit' then N'0'
								when t.name in ('char', 'varchar') then N'C'
								when t.name in ('date', 'datetime', 'datetime2', 'datetimeoffset') then N'1990-01-01'
								when t.name in ('decimal', 'numeric') then N'0'
								when t.name in ('float', 'real') then N'0'
								when t.name = 'image' then N'0x0'
								when t.name in ('money', 'smallmoney') then N'0'
								when t.name in ('nchar', 'nvarchar') then N'N'
								when t.name in ('ntext', 'text') then N'T'
								when t.name = 'smalldatetime' then N'1990-01-01'
								when t.name = 'sql_variant' then N'0'
								when t.name = 'time' then N'12:00:00'
								--when t.name = 'timestamp' then N'' -- Doesn't get inserted
								when t.name = 'uniqueidentifier' then N'00000000-0000-0000-0000-000000000000'
								when t.name = 'xml' then N''
								--when t.name = 'geography' then N'' -- Not supported
								--when t.name = 'geometry' then N'' -- Not supported
								when t.name = 'hierarchyid' then N'/'
								when t.name = 'sysname' then N'S'
							end,
							(case when t.name not in ('binary', 'image', 'varbinary') then N'''' else N'' end),
							N''
						),
						N', ')
				from sys.columns c
				inner join sys.types t on c.user_type_id = t.user_type_id
				where c.object_id = @TableObjectId and t.name != 'timestamp' and c.is_computed = 0 and c.is_nullable = 0
			),
			N');
'
		);

	if (@HasIdentityColumn = 1)
	begin
		select @Query +=
			concat(
				N'set identity_insert ',
				quotename(@TableSchema), N'.', quotename(@TableName),
				N' off;'
			);
	end

	print N'Running query:';
	print @Query;
	exec sp_executesql @stmt = @Query;

	print N'';
	print N'';

	fetch next from curs into @TableObjectId, @TableSchema, @TableName, @HasIdentityColumn;
end;

return 0;

go



exec #ToggleTriggers @Enabled = 0;
--select * from sys.triggers;

exec #ToggleCheckConstraints @Enabled = 0;
--select * from sys.check_constraints;

exec #ToggleForeignKeys @Enabled = 0;
--select name, is_disabled, is_not_trusted from sys.foreign_keys;

exec #DeleteTables;
--select * from HumanResources.Department;

exec #InsertRows;

exec #ToggleForeignKeys @Enabled = 1;
--select name, is_disabled, is_not_trusted from sys.foreign_keys;


-- Not worried about trying to turn these on
--exec #ToggleCheckConstraints @Enabled = 1;
--select * from sys.check_constraints;

exec #ToggleTriggers @Enabled = 1;
--select * from sys.triggers;


exec sp_MSforeachtable 'select ''TableName = ?'' as [?], * from ?;';