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.
Author |
Topic |
bholmstrom
Yak Posting Veteran
76 Posts |
Posted - 2013-04-01 : 15:55:36
|
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 10Line 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_NAMEAny dieas, and thanks in advanceBryan Holmstrom |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-01 : 16:22:32
|
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 OptimizerTG |
|
|
bholmstrom
Yak Posting Veteran
76 Posts |
Posted - 2013-04-01 : 16:31:06
|
The goal here is to run thru a database and print the column names for each table to a flat file.Bryan Holmstrom |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-01 : 16:58:08
|
Then perhaps this query with results directed to file:select table_name, column_name from information_schema.columns order by table_name, ordinal_positionBe One with the OptimizerTG |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-04-01 : 18:30:53
|
Just as an aside, the OBJECT_ID function does accept a variable name as a parameter. You could have code like:[CODE]declare @TABLE_NAME sysname = 'MyTable';SELECT name FROM syscolumns WHERE id=OBJECT_ID(@TABLE_NAME);[/CODE]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 |
|
|
Prakash Machiraju
Starting Member
2 Posts |
Posted - 2013-04-02 : 10:08:43
|
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 TableCursorFETCH TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGIN SET @ColNames = '' SELECT @ColNames = COALESCE(@ColNames + ', ' + COLUMN_NAME, Column_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName PRINT @ColNames FETCH TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGOPrakash Machiraju |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-02 : 11:04:46
|
TG's is one line and doesn't use a cursor. |
|
|
|
|
|
|
|