Tsql for a stor proc - loop through tables in a database

Transact-SQL
1

Hi,

I am writing a stor proc for a Sql Server 2014 DB that will loop through the tables and then do an insert using each table name returned from the schema select.
I will be inserting the table (and its content) in another database but first dropping the table in the new database.
I know how to pull the tables names using the below select but am unsure on the best way to loop through the tables to do the drop and insert.

Any advise on the best way to do this in a stor proc?

Thanks

SELECT * FROM DB1.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

2

sp_foreachtable

HOwever, the job is not that simple. If you truly want to replicate a table, you also have to think about:

  1. triggers
  2. computed columns
  3. foreign keys
  4. indexes
  5. partition schemes
  6. compression options
  7. ... and I'm sure I'm missing a few
3

Hi,

I should have said this is only to take a copy of the content in the rows. There is a proper backup in place for full dB restore.
Thanks

4

is the cheapest way to go. unless this is for production, since it's not officially supported.