Results 1 to 4 of 4

Thread: Any Gurus...?

  1. #1
    Join Date
    Sep 2002
    Posts
    51

    Arrow Unanswered: 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!"
    When in doubt just ask your self,
    -WWSBD?-
    (what would Sponge Bob do?)

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    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.
    Last edited by Coolberg; 11-25-02 at 09:13.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Any Gurus...?

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

  4. #4
    Join Date
    Nov 2002
    Posts
    6

    something like this....

    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
    Last edited by Spinxxx; 11-25-02 at 17:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •