TopAnswers Databases
or
My wife has a request for a copy of a database where each table only has 10 rows in it.

I suggested she loop through the tables, something like this:

    declare @table varchar(255),
    @stgtable varchar(255),
    @columns varchar(max),
    @sql nvarchar(max)

    declare c cursor for

    select table_name from INFORMATION_SCHEMA.tables

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @sql = 'alter table dbo.' + @table + ' nocheck constraint all'

    print @sql
    --exec sp_executesql @sql

    select @columns = stuff( (
        select ', ' + Column_Name from information_schema.columns
        where Table_Name = @table
        for xml path ('')),1,2,'')

    set @sql = 'use sampledb; select top 10 ' + @columns + ' into ' + @table + ' from 	
    reportDB.dbo.' + @table 

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table
    end

    close c
    deallocate c

but this seems kind of loopy, if you know what I mean.

**QUESTION** is there a more straightforward way?
PeterVandivier
etc. etc.
PeterVandivier
also just cause we're still in this room and to be on topic - i was thinking that if you just used your script as-written, you could still deploy the FKs as untrusted if you wanted them to get picked up by a database diagram on the other end, although indexes / triggers / user code is still left on the table
PeterVandivier
i guess i was using 2017 RCs quite a bit and then did a 2017 upgrade pretty close after RTM
James
haha, we are still in the process of upgrading to 2016
PeterVandivier
i haven't even worked on SQL Server in the last 7 months! 😂
PeterVandivier
no... seriously? i feel like i've been using it forever
James
ohhh, string_agg() is for sql server 2017 and up - that came out what, 7 months ago?
James
lol
PeterVandivier
bug report incoming :-P
PeterVandivier
hmm... leaving and coming back has caused the one-boxing to break for the 2 subsequent links
James
the url still works though
James
yea, I think the fiddle broke.
PeterVandivier
...multiples get pretty easy to read as well
PeterVandivier
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5c0540e9752e10993ce1ea44d5356a94&hide=1
PeterVandivier
in any case, i'm pretty sure you can do this with the `xml path` syntax as well. but just as a sweeter for the `string_agg()` option...
PeterVandivier
and in the one-box'd permalink
PeterVandivier
::: quote 644 16159 38 d4dfec 3e6799
PeterVandivier[ *— a day ago*](#c16159)  
><>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f02f16b03838e5b2eafd717bb2533fd8
:::
PeterVandivier
still renders in the transcript though 🤔
PeterVandivier
or just me?
PeterVandivier
![Screenshot 2020-01-30 at 14.03.59.png](/image?hash=8de2a472b8e722fe9ebad4073d196be1724d6eef41c30fa4e30f848f4aaac3b2)
PeterVandivier
oh weird - did the embedded fiddle one-box break for you too?
PeterVandivier replying to James
lol, i get it. i did the same thing even when i first learned about it. it's like writing "2019" for dates all through january and into feb
James
hmm, i'll have to look into string_agg(), i've been luggging that stuff function around for a decade 
PeterVandivier
also looks like `select *` would work in your original cursor as well fwiw
PeterVandivier
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f02f16b03838e5b2eafd717bb2533fd8
PeterVandivier
also, I'm assuming you're aware of `string_agg()` in lieu of `for xml path`, just on an older version?
PeterVandivier
at a sample depth of 10 rows. galaxy brain thinking there
James
i think it's one hospital bought another, and now they want to see what they bought
James
lol yea, i'll ask too
PeterVandivier
sounds rather like an X-Y problem sort of ask, you know?
PeterVandivier replying to James
out of curiousity, what's it for? somebody just wants seed data?
James
yea, that would work
PeterVandivier
but you see what i mean - like, leave the outer dimension entities until last and leave a few extra rows even if they don't have referencing lookups
PeterVandivier
you can't risk orphans if the parents are dead first (last? my morbid analogy is falling apart)
PeterVandivier
where all deletes are "_delete all but 10_"
PeterVandivier
e.g.  
* loop 1  
  * delete from `child2` ( fails )  
  * delete from `child1` ( fails )  
  * delete all but 10 from `parent` / rm `parent` from queue
* loop 2  
  * delete from `child2` ( fails )  
  * delete from `child1` / rm `child1` from queue  
* loop 3  
  * delete from `child2`, queue empty
PeterVandivier
also if you restore the whole thing and go backwards
PeterVandivier replying to ypercubeᵀᴹ
i think it might still be trivial as long as you swallow errors and retry all failures recursively ( to a certain depth ) 
ypercubeᵀᴹ
@James the problem is not trivial if you consider FK constraints.
Josh Darnell
You might want to consider editing your question.
Josh Darnell
Well, it can be easy to get caught up in a potential solution, to the point of losing focus on the original problem.
James
yea, i probably should have written it like that lol
Josh Darnell
So your actual question is "what's a straightforward way to send a copy of a database to someone, with all of the data removed except 10 rows from each table?"
James
i guess she's got to take a full backup, then delete all but 10 rows from each table?
James
oh, you're right
James
oh, I thought it gave you all the tables, only empty
Josh Darnell
> My wife has a request for a copy of a database  
  
Does this mean she is asking for a copy of a database, or she needs to send someone a copy of a database?
Josh Darnell
@James I don't understand your question.  `RESTORE HEADER ONLY` doesn't actually restore anything, it just returns metadata about a backup file.