SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Using Variables in Cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 04/01/2013 :  15:55:36  Show Profile  Reply with Quote
Good afternoon, I am trying to use a set of variables to declare a cursor, the error is:

Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@tmp_cursor'.

The code so far is:

DECLARE @TABLE_NAME varchar(50),
@TMP_CURSOR varchar(100)

SELECT @table_name='saleslogix.sysdba.c_acct_marketing_100'+')'
SELECT @TMP_CURSOR='CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('

-- DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('saleslogix.sysdba.c_acct_marketing_100')
PRINT @TMP_CURSOR + @TABLE_NAME

DECLARE crs @tmp_cursor + @TABLE_NAME

Any dieas, and thanks in advance

Bryan Holmstrom

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 04/01/2013 :  16:22:32  Show Profile  Reply with Quote
you can't embed dynamic sql withing non-dynamic sql.
Can you explain (big picture) what you're trying to accomplish. I'm sure there is a better way.

Be One with the Optimizer
TG
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 04/01/2013 :  16:31:06  Show Profile  Reply with Quote
The goal here is to run thru a database and print the column names for each table to a flat file.

Bryan Holmstrom
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 04/01/2013 :  16:58:08  Show Profile  Reply with Quote
Then perhaps this query with results directed to file:

select table_name, column_name from information_schema.columns order by table_name, ordinal_position

Be One with the Optimizer
TG
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1646 Posts

Posted - 04/01/2013 :  18:30:53  Show Profile  Reply with Quote
Just as an aside, the OBJECT_ID function does accept a variable name as a parameter. You could have code like:
declare @TABLE_NAME sysname = 'MyTable';
SELECT name FROM syscolumns WHERE id=OBJECT_ID(@TABLE_NAME);
Not to be completely pedantic but the data type for a schema object, like a table, is "sysname". You'll be glad later when the length of your table names grows beyond 50 characters and your code still works.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

Prakash Machiraju
Starting Member

United Kingdom
2 Posts

Posted - 04/02/2013 :  10:08:43  Show Profile  Reply with Quote
Please try the following script.

Script to get the column names for all the tables in a given database.

DECLARE @TableName VARCHAR(100)
DECLARE @ColNames VARCHAR(MAX)
DECLARE @DBName VARCHAR(100)

SET @DBName = '<YourDBName>'

DECLARE TableCursor CURSOR FOR
SELECT TABLE_Name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @DBName
AND TABLE_TYPE = 'BASE TABLE'

OPEN TableCursor

FETCH TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @ColNames = ''

SELECT @ColNames = COALESCE(@ColNames + ', ' + COLUMN_NAME, Column_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

PRINT @ColNames

FETCH TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
GO

Prakash Machiraju
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/02/2013 :  11:04:46  Show Profile  Visit russell's Homepage  Reply with Quote
TG's is one line and doesn't use a cursor.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000