SQL Server Performance

Query more tables together

Discussion in 'SQL Server 2005 General DBA Questions' started by ismailadar, Aug 9, 2010.

  1. ismailadar New Member

    Hi all,
    I have a lot of tables in a database.I need top 1 record of all tables that table name ands with 'en' and i want to query this tables from sys.tables system table
    Select name from sys.tables where name like '%en'
    with this query i can list all the table names i need and how can i query the top 1 records from these tables.?
    thanx in advanced
  2. ashish287 New Member

    use cursor like below....declare
    @name varchar(200),@cmd nvarchar(max)declare c1 cursor for
    name from sysobjects where xtype = 'u'open
    next from c1 into @namewhile
    @@FETCH_STATUS = 0beginprint
    'tablename is ' + @nameset
    @cmd = 'select top 1 * from ' + @nameprint
    sp_executesql @cmdprint
    next from c1 into @nameend
    it have all the required details which you want to see. But you can delete any print which you not want to see.
  3. ashish287 New Member

    bit modification....
    i used query as
    select name from sysobjects where xtype = 'u'
    you can use your select statement but make sure you selecting only one value because thats what we passing into cursor.
    Hope you understand what i mean
  4. ismailadar New Member

    Using cursor is the worst way and i do not prefer using Cursor . Is there way way to achieve this?
  5. ashish287 New Member

    what do you mean by worst way? Have you analyzed your query plan after running this cursor? Or is it just what you read in book?
  6. ismailadar New Member

    i have not looked at query plan yet. i had used cursor before and i saw it has a bad performance so i did not no prefer this way.i can believe we can make everything without using cursors. we can chose another way instead of cursor can't it?
  7. FrankKalis Moderator

    To be honest, the requirement sounds like a one-off requirement to me anyway and for such a thing a cursor is "good enough" as it gets the task off your list.
    Another way would be to
    SELECT 'SELECT TOP 1 * FROM ' + NAME FROM sys.sysobjects S WHERE S.name LIKE '%en' AND S.xtype = 'U'
    then copy the query result into a new query window and execute it.
    Btw, you know that TOP 1 without any ORDER BY is really meaningless? Or is it just a check to see if there is some data at all?
  8. ashish287 New Member

    if you so afraid of curor :)
    then you will getting the query which will be running with cursor by print @cmd
    you can take help of this print and run all those syntax in new window

Share This Page