How to use for in this SQL - 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 2005 Forums

Transact-SQL (2005)

How to use for in this SQL

Author

Topic

Nith
Starting Member

17 Posts

Posted - 2010-06-03 : 06:38:51

Dear buddy,

I need to display all the tables in a database and the columns (separated by coma).

This sql gives me the right output only for the 1st table, how can I get it into a loop so that it goes through all the records? I tried a few ways and failed.

DECLARE @Description varchar(1000)
DECLARE @TableName char(100)

/* Define the cursor that can be used to access the records of the table,row by row */

DECLARE curTable cursor for
SELECT TableName from Temp_TableLists

-- Open the cursor

OPEN curTable

-- Fetch the rows into variables

FETCH curTable into @TableName
select @Description = coalesce(@Description + ', ' , '') + CAST(column_name AS varchar(50))
FROm information_schema.columns
where information_schema.columns.table_name = @TableName
select @TableName, @Description
--Fetch the next row from the cursor
FETCH curTable into @tableName

-- Close the cursor
CLOSE curTable
-- Deallocate the cursor
DEALLOCATE curTable
----------------
Another method I tried gives me all the tables correctly but the same column list for all the tables.

DECLARE @Description varchar(1000)
DECLARE @TableName char(100)

/* Define the cursor that can be used to access the records of the table,row by row */

DECLARE curTable cursor for
SELECT TableName from Temp_TableLists

-- Open the cursor

OPEN curTable

-- Fetch the rows into variables

FETCH curTable into @TableName
WHILE @@FETCH_STATUS = 0
begin
select @Description = coalesce(@Description + ', ' , '') + CAST(Column_Name AS varchar(50))
FROm information_schema.columns
where information_schema.columns.table_name = @TableName

--Fetch the next row from the cursor
FETCH curTable into @tableName
select @TableName, @Description
end

-- Close the cursor
CLOSE curTable
-- Deallocate the cursor
DEALLOCATE curTable

Please guide me.

Nith

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 06:49:12

Warning captain... Cursors AHOY!

Try this instead if you are on 2005 or greater!


SELECT
	QUOTENAME(t.[table_name]) AS [Table]
	, LEFT(cols.[colNames], LEN(cols.[colNames]) - 1 ) AS [Columns]
FROM
	information_Schema.tables AS t

	CROSS APPLY (
		SELECT
			QUOTENAME(c.[column_name]) + ', '
		FROM
			information_schema.columns AS c
		WHERE
			c.[table_name] = t.[table_name]
		ORDER BY
			c.[ordinal_position]
		FOR
			XML PATH('')
		)
		cols ([colNames])

It's safe to run and it should give you quickly the kind of output you need.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Nith
Starting Member

17 Posts

Posted - 2010-06-03 : 07:02:24

Thanks for the quick reply but I don't understand on how I should be using it.

Any examples?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 07:04:35

Just copy and paste the code into a management studio window open on the database you are interested in and run it. You'll get a result set.

What are you doing to run the code you posted first?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Nith
Starting Member

17 Posts

Posted - 2010-06-03 : 21:46:09

Managed to get it working!

Thank You very much!!!!!

Thats really cool!!

Nith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources