querry without knowing table name

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 2000 Forums

SQL Server Development (2000)

querry without knowing table name

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-02 : 07:10:39

samit writes "I was asked in an interview whether I could extract information from a database without knowing the table names.

I could not find an asnwer so if you could reply to me.

Thanking you
samit kumbhani"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-02 : 07:27:34

[code]
select 'select * from ' + name
from sysobjects
where xtype = 'u'
[/code]

copy the result of the above query in to a fresh QA screen and you got what you want.

JellyRoll
Starting Member

8 Posts

Posted - 2004-06-02 : 11:28:52

Yup.

You could SELECT table names from the sysobjects table, which is the system table in each database where SQL Server keeps information about tables, views, etc. into a cursor, then fetch each entry.

SELECT * from @tablename, or use another cursor to get the column names from syscolumns and build a SELECT query on the fly.
Tres cool, no?

samit
Starting Member

2 Posts

Posted - 2004-06-02 : 11:58:19

yeah that is what i meant that i could i retrive sata from the tables wihout knowing the tables name.

I also found a querry from one of you saying about the cursor but i could not make much out of it. could some one give a more detail explanantion as i am novice to this field.

thanks for the help

samit

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-02 : 12:01:14

Why did you go for the interview in the first place if you don't know SQL Server??

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-02 : 12:36:52

[code]
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@dbName sysname
, @fp varchar(255)
, @User varchar(255)
, @Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
+ '-f'+@fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @CMD

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CMD
SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
EXEC master..xp_cmdshell @CMD
SELECT @CMD = @fp + '\bcpout.bat'
SELECT @CMD
insert a (s)
exec master..xp_cmdshell @cmd

FETCH NEXT FROM bcpout INTO @CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2

[/code]

Brett

8-)

samit
Starting Member

2 Posts

Posted - 2004-06-03 : 14:09:31

tx a lotg grp for the help

it would definately help me

thanks again

samit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources