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 |
amirs
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-05-09 : 02:09:21
|
hii 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.tablesMadhivananFailing to plan is Planning to fail |
|
|
eralper
Yak Posting Veteran
66 Posts |
Posted - 2008-05-09 : 03:01:32
|
Another statement:select * from sys.tables-------------Eralperhttp://www.kodyaz.com |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2008-05-10 : 01:29:41
|
hi,Select table_name from information_schema.columns |
|
|
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. |
|
|
amirs
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-05-11 : 23:41:14
|
thanks to all for replaybut i read table in database in while condition and store in variablei solve this problem in following scriptUSE Database nameDECLARE @sql nvarchar(4000)DECLARE tnames_cursor CURSORFOR SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE' OPEN tnames_cursorDECLARE @tablename sysnameFETCH NEXT FROM tnames_cursor INTO @tablenameWHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT @tablename = RTRIM(@tablename) END FETCH NEXT FROM tnames_cursor INTO @tablenameENDCLOSE tnames_cursorDEALLOCATE tnames_cursor |
|
|
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 replaybut i read table in database in while condition and store in variablei solve this problem in following scriptUSE Database nameDECLARE @sql nvarchar(4000)DECLARE tnames_cursor CURSORFOR SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE' OPEN tnames_cursorDECLARE @tablename sysnameFETCH NEXT FROM tnames_cursor INTO @tablenameWHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT @tablename = RTRIM(@tablename) END FETCH NEXT FROM tnames_cursor INTO @tablenameENDCLOSE tnames_cursorDEALLOCATE tnames_cursor
Why a cursor here? You can do this with WHILE loop as wellDECLARE @TableName sysname SELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'WHILE @TableName IS NOT NULLBEGIN--your other codeSELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'AND TABLE_NAME > @TableNameEND |
|
|
amirs
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-05-12 : 01:10:23
|
thanks visakh16i use your script reduce my code increse performance and work well |
|
|
|
|
|