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
 General SQL Server Forums
 New to SQL Server Programming
 Read table name in sql databse

Author  Topic 

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2008-05-09 : 02:09:21
hi
i want read table name in database in sql while loop statement

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-09 : 02:48:30
Seselect table_name from information_schema.tables


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-09 : 03:01:32
Another statement:

select * from sys.tables


-------------
Eralper
http://www.kodyaz.com
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-10 : 01:29:41
hi,


Select table_name from information_schema.columns
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 01:36:18
quote:
Originally posted by raky

hi,


Select table_name from information_schema.columns


This will have records for each column of table so you will get duplicate table_name. So you have to use distinct if you want to use this to get table names. You dont really have to use this view unless you need some column related information. You can use the information_schema.tables itself as madhi suggested.
Go to Top of Page

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2008-05-11 : 23:41:14
thanks to all for replay
but i read table in database in while condition and store in variable
i solve this problem in following script


USE Database name
DECLARE @sql nvarchar(4000)
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'

OPEN tnames_cursor
DECLARE @tablename sysname

FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)

END

FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 00:12:33
quote:
Originally posted by amirs

thanks to all for replay
but i read table in database in while condition and store in variable
i solve this problem in following script


USE Database name
DECLARE @sql nvarchar(4000)
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'

OPEN tnames_cursor
DECLARE @tablename sysname

FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)

END

FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor



Why a cursor here? You can do this with WHILE loop as well

DECLARE @TableName sysname
SELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'
WHILE @TableName IS NOT NULL
BEGIN
--your other code

SELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'
AND TABLE_NAME > @TableName

END
Go to Top of Page

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2008-05-12 : 01:10:23
thanks visakh16
i use your script reduce my code increse performance and work well
Go to Top of Page
   

- Advertisement -