x

Please Explain Script

Hello,

Can you please explain to me what the script below does? If possible, line by line.

Thank you in advance.

** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** **

set nocount on

go

select distinct 'Print ''Start: ' + a.name + '''' + 
char(13) +  'Insert into Databasename.PROD_OCS.' + 
a.name + char(13) +  ' Select * From Databasename.PROD_OCS.' + a.name  + char(13)
        from sys.sysobjects a, sys.sysindexes b   
where a.xtype = 'U'     
and a.id = b.id 
and b.rowcnt > 0
more ▼

asked May 08 '12 at 11:20 PM in Default

eRock gravatar image

eRock
1 1 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I'm working under the presumption that "Databasename" for the insert into Databasename.PROD_OCS is supposed to be different than "Databasename" for the select * from Databasename.PROD_OCS

For each user table (that's the "sys.sysobject a" bit) in the current database that has any rows ("sys.sysobjects b" and "and b.rowcnt > 0"), it is generating an insert into select statement that, were you to execute it, would load the table in the PROD_OCS schema of one database from the identically named table in the PROD_OCS schema, having the same number and type of columns, in another database.

It's using DISTINCT because the join between a and b can result in multiple rows for each table (a table can have 1 or more rows in sys.indexes), but the script only wants one "insert into select" statement per table.

Because this snippet is just generating the statements and not executing them, it's safe to run as you have presented it. Then you can take a look at the output to see what it would do.
more ▼

answered May 09 '12 at 03:17 AM

KenJ gravatar image

KenJ
19.1k 1 3 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x265

asked: May 08 '12 at 11:20 PM

Seen: 478 times

Last Updated: May 09 '12 at 01:06 PM