Any Gurus...?

I asked the following question in the 'MS Access' boards but I am wondering if there is a way to do it on our SQL server as well? Heres the original question:

"Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following:

1. write a query on the fly for each table in the database (without knowing the table names ahead of time)

2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time

I have searched news groups and web rings all night. Please help!"You'll get the tables by searching the table Sysobjects for all objects whose xtype is "U". Write a SP that does a cursor loop through all records satisfying that search and, for each match, runs that query that you want to run on all tables.

To concatenate all fields, I guess you'll need to first investigate the columns' data types and convert non-string types to strings? And handle null values? And handles non-convertable types?
Start with the Syscolumns column...

Check first if there are any system SP's that could help you avoid having the code Select's directly against system tables.|||I asked the following question in the 'MS Access' boards but

Q1 I am wondering if there is a way to do it on our SQL server as well?

Heres the original question: "Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following: 1. write a query on the fly for each table in the database (without knowing the table names ahead of time) 2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time I have searched news groups and web rings all night. Please help!"

A1 Yes, it is certainly possible (in Sql Server and in MS Access). The original #2. may entail work arounds (with many very long table names, especially if these include spaces and / or special characters)

The applicable special stored procedures (Sql Server 2000 sp_) would be sp_tables and sp_columns. One may also make use of [INFORMATION_SCHEMA] views to address your tasks.

-- Example useages of sp_tables and sp_columns:

Use Pubs
Go

Exec sp_tables
Exec sp_tables @.table_type = ['Table']
Exec sp_tables @.table_type = ['View']
Exec sp_tables @.table_type = ['System Table']

Exec sp_columns @.table_name = 'Authors'
--------

-- General List: Catalog Special Stored Procedures:
sp_column_privileges
sp_special_columns
sp_columns
sp_sproc_columns
sp_databases
sp_statistics
sp_fkeys
sp_stored_procedures
sp_pkeys
sp_table_privileges
sp_server_info
sp_tables
--------

-- INFORMATION_SCHEMA views (return metadata of DB objects):

CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS

----
Note: Selecting data from information schema views requires using an [INFORMATION_SCHEMA] qualified object name i.e.(in the position where one normally specifies nothing, or the dboo name as appropriate). For example:

SELECT *
FROM Master.[INFORMATION_SCHEMA].COLUMNS
----

-- THE FOLLOWING would fail (unless someone has created a user table / view named 'COLUMNS'):

SELECT *
FROM Master..COLUMNS

SELECT *
FROM Master.dbo.COLUMNS
--------|||Just an example... Improvement and tidying could be made I'm sure!

declare @.id int,
@.name varchar(255),
@.transaction varchar(8000),
@.queryField varchar(8000),
@.col_list varchar(8000),
@.tab_name varchar(255),
@.col_err real,
@.tab_err real

declare table_scan cursor for
select name, id
from sysobjects
where type = 'U'

open table_scan

fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
PRINT @.tab_err

while (@.tab_err = 0 )
begin
DECLARE column_scan CURSOR FOR
SELECT name
FROM syscolumns
WHERE ID = @.id

OPEN column_scan
FETCH column_scan into @.name

SELECT @.col_list = @.name
SELECT @.queryField = 'queryfield:['+@.name+']'

SELECT @.col_err = @.@.FETCH_STATUS

WHILE (@.col_err = 0)
begin
SELECT @.col_list = @.col_list +', ' + @.name
SELECT @.queryField = @.queryField + '&['+@.name+']'

FETCH column_scan into @.name
set @.col_err = @.@.FETCH_STATUS
end
SELECT @.transaction = 'SELECT '+ @.col_list + ' FROM ' + @.tab_name
PRINT '================================================= '
PRINT @.transaction
print @.QueryField
PRINT '================================================= '
EXEC(@.transaction)

close column_scan
deallocate column_scan
--
--Table completed
--
fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
end

close table_scan

deallocate table_scan