System procedure, sp_columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

System procedure, sp_columns

Author

Topic

Codesensitive
Starting Member

11 Posts

Posted - 2002-04-06 : 09:30:00

Hi, I need some help with a stored procedure using the system procedure sp_columns. In my application I dynamicly create new database tables and to keep track on the dynamic tables and it's columns I have two tables, "tables_4003" and "table_columns_4004". Each record in "tables_4003" represents a dynamic table and "table_columns_4004" the dynamic tables columns linked together with a reference id.

What I want to do is to update a dynamic table towards the information in the "tables_4003" and "table_columns_4004". So when I run the procedure I want the check the actual columns in the dynamic table with sp_columns and compare it the columns in "table_columns_4004". Add all any new columns to the dynamic table and remove any column that's not in the "table_columns_4003".

I tried to create a cursor and step through each record returns from the sp_columns, but the exec command doesn't seem to work very well together with a cursor.

Any ideas on how to write this procedure?
I'm really grateful for any information on this.
Thanks alot.

Jay99

468 Posts

Posted - 2002-04-06 : 09:38:22

There are two good ways to ask 'What is in TableA that is not in TableB'


select
    a.<columnlist>
from
    TableA a
    left join TableB b
        on a.<key> = b.<key>
where
    b.<key> is null


select
    a.<columnlist>
from
    TableA a
where
    not exists (
    select 1
    from
        TableB b
    where
        b.<key> = a.<key>

Does that help?

Jay
<O>

Codesensitive
Starting Member

11 Posts

Posted - 2002-04-06 : 10:02:18

Well, is it possible to use exec in this clause?

select a.<columnlist>
from TableA a
where
not exists (
select COLUMN_NAME
from
EXEC sp_columns 'TableB'
where
COLUMN_NAME = a.<key>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-06 : 10:18:59

You can't use EXEC in a SELECT statement like that, but you can get the same kind of output from INFORMATION_SCHEMA.COLUMNS:

SELECT A.* FROM INFORMATION_SCHEMA.COLUMNS A LEFT JOIN
INFORMATION_SCHEMA.COLUMNS B ON (A.COLUMN_NAME=B.COLUMN_NAME)
WHERE A.TABLE_NAME='tables_4003' AND B.TABLE_NAME='table_columns_4004'
AND B.COLUMN_NAME IS NULL

You'll probably have to tweak this a little bit, it's still early and my brain is still cobwebby

Nazim
A custom title

1408 Posts

Posted - 2002-04-06 : 10:25:02

i dont think you can use exec in a from clause directly.

you can use openrowset and play with it.

eg:

select *
from author a
where
not exists (
select *
from
OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'EXEC sp_columns ''TableB'' ') AS a where
COLUMN_NAME = a.<key> )

HTH

--------------------------------------------------------------

Edited by - Nazim on 04/06/2002 23:31:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources