stored procedure varaiables

[Stevie D]

Hi everyone,

I'm learning T-SQL and I have created a copy of the pubs database called
pubs2.

I both the original pubs database and pubs2 I have created an identical view
called TableFields.

In the database in which it resides this view retrieves: Every table in the
database as WS_TABLE, and Every field in the database as WS_FIELD (it also
retrieves the datatype and length of the field but that isn't important
right now).

I have been creating and dropping tables in pubs2 and also adding and
deleting fields.

I have written a SQL statement that compares the two databases and lists the
tables that are in pubs but not in pubs2

SELECT DISTINCT DB1.WS_TABLE, DB2.WS_TABLE
FROM pubs..TableFields DB1 LEFT JOIN pubs2..TableFields DB2 ON DB1.WS_TABLE
= DB2.WS_TABLE
WHERE DB2.WS_FIELD IS NULL

I would like to turn this statement into a stored procedure called
sfp_DB_Table_Compare which accepts the databases to be compared as
variables.

Ideally the syntax would be something like:

EXECUTE sfp_DB_Table_Compare pubs, pubs2

My failed attempt is as follows: -

CREATE PROCEDURE sfp_Db_Table_Compare
(@DBase1 varchar(20), @DBase2 varchar(20))
SELECT DISTINCT DB1.WS_TABLE, DB2.WS_TABLE
FROM DB1..TableFields DB1 LEFT JOIN DB2..TableFields DB2 ON DB1.WS_TABLE =
DB2.WS_TABLE
WHERE DB1 = @DBase1
AND DB2 = @Dbase2
AND DB2.WS_FIELD IS NULL
RETURN

Can anybody help me please?

[John Bell]

Hi

You would have to resort to dynamic SQL to do this as you can't use a
variable as the database name.
Check out http://www.sommarskog.se/dynamic_sql.html for a discussion on
using dynamic SQL.

John

"Stevie D" <St***@127.0.0.1> wrote in message
news:d1**********@news7.svr.pol.co.uk...

Hi everyone,

I'm learning T-SQL and I have created a copy of the pubs database called
pubs2.

I both the original pubs database and pubs2 I have created an identical
view
called TableFields.

In the database in which it resides this view retrieves: Every table in
the
database as WS_TABLE, and Every field in the database as WS_FIELD (it also
retrieves the datatype and length of the field but that isn't important
right now).

I have been creating and dropping tables in pubs2 and also adding and
deleting fields.

I have written a SQL statement that compares the two databases and lists
the
tables that are in pubs but not in pubs2

SELECT DISTINCT DB1.WS_TABLE, DB2.WS_TABLE
FROM pubs..TableFields DB1 LEFT JOIN pubs2..TableFields DB2 ON
DB1.WS_TABLE
= DB2.WS_TABLE
WHERE DB2.WS_FIELD IS NULL

I would like to turn this statement into a stored procedure called
sfp_DB_Table_Compare which accepts the databases to be compared as
variables.

Ideally the syntax would be something like:

EXECUTE sfp_DB_Table_Compare pubs, pubs2

My failed attempt is as follows: -

CREATE PROCEDURE sfp_Db_Table_Compare
(@DBase1 varchar(20), @DBase2 varchar(20))
SELECT DISTINCT DB1.WS_TABLE, DB2.WS_TABLE
FROM DB1..TableFields DB1 LEFT JOIN DB2..TableFields DB2 ON DB1.WS_TABLE =
DB2.WS_TABLE
WHERE DB1 = @DBase1
AND DB2 = @Dbase2
AND DB2.WS_FIELD IS NULL
RETURN

Can anybody help me please?

[David Portas]

If you want to compare table structures in two databases I suggest you
make use of the Information Schema views. They should provide all the
information you need. You might alternatively consider one of the DB
tools on the market that will help you compare schemas and synchronize
them. See www.red-gate.com for example.

SELECT A.*, B.*
FROM DB1.information_schema.columns AS A
FULL JOIN DB2.information_schema.columns AS B
ON A.table_schema = B.table_schema
AND A.table_name = B.column_name
AND A.column_name = B.column_name
WHERE
COALESCE(A.is_nullable,'')<>COALESCE(B.is_nullable ,'')
OR COALESCE(A.data_type,'')<>COALESCE(B.data_type,'')
... etc

The bad news is that to parameterise the database names you will most
likely want to use Dynamic SQL. Dynamic SQL is really something you
should try to avoid in a production application. I assume that
comparing databases is something you'll only want DB admins to do
rather than users so it's probably a case where Dynamic SQL is a
reasonable solution. Still, if you haven't written dynamic code before
you should read and absorb Erland's superlative article on the subject.
It has all you need to know:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--