Solved

Is it possible to find the structure of a Global Table which is created in Proc A and referenced in Proc B

Posted on 2008-10-20
5
207 Views
Last Modified: 2012-05-05
I have a global table created in proc A and then referenced in proc b.

I would like to know in Proc B, if the global table has a particular column, how can I do that
Proc A
INSERT INTO #Activity
EXEC dbdetails.dbo.Proc_B
    @TableName = @GlobalTableName_parm, 
    @ServerName = @@SERVERNAME,
    @UpdateFlag = 1,
    @SuccessFlag = @SuccessFlag OUTPUT
 
Proc_B
CREATE PROCEDURE Proc_B
    @TableName  varchar(100),
    @ServerName varchar(50),
    @UpdateFlag int,
    @SuccessFlag tinyint OUTPUT
AS
 
I would like to know the sql that I can excute to verify that @TableName  has a particular column say 'Test'

Open in new window

0
Comment
Question by:countrymeister
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:randy_knight
ID: 22762570
What do you mean by global table?  A global temporary table (i.e. ##table)?
0
 
LVL 1

Author Comment

by:countrymeister
ID: 22762599
Yes , the global table is named ##TableA_spid
0
 
LVL 4

Expert Comment

by:randy_knight
ID: 22762775
I don't think there is an easy way to do that.  However, an altenative would be to create your global temp tables as hard tables in tempdb (i.e. tempdb.dbo.TableA_spid) and then drop them at the end of your process.  

Then you could used this to find the column:



if exists(select * from tempdb.dbo.syscolumns where id = object_id('TableA_spid' and name = 'test')
    print 'column exists'
else
    print 'column does not exist'

Open in new window

0
 
LVL 1

Author Comment

by:countrymeister
ID: 22762916
I am creating the global table as follows
SELECT @GlobalTableName = '##TableA_' + convert(varchar(10),@@spid)

SELECT @EXECSQLText = N'CREATE TABLE ' + @GlobalTableName
( col1, col2....)

I am droping them after I use them

But the following sql does not work to check if the column exists in the global table, where @TableName is the global table

Set @IncludeTaxes = 0
-- Check if the calling proc has passed the test column  to the  proc B
Set @IncludeTaxes = (SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = 'Test')
0
 
LVL 4

Accepted Solution

by:
randy_knight earned 500 total points
ID: 22763480
Right.  Because INFORMATION_SCHEMA.COLUMNS won't have info on temporary tables.  What I am saying is create a "permanent" table in tempdb like this:

SELECT @GlobalTableName = 'TableA_' + convert(varchar(10),@@spid)

Then it will be in syscolumsn and the DSV above.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Amazon RDS migrate to SQL Server 3 24
Powershell v3 - SQLCMD 3 26
Download ms sql express. 2 23
Error when creating an UPDATE Trigger in SQL 6 18
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question