SQL Server Performance

Some doubts about owners...

Discussion in 'General DBA Questions' started by sunda, Feb 27, 2009.

  1. sunda New Member

    Good Morning to all!!!
    First Question:
    Does anyone knows how to use (i dont know the name in english, in portuguese is macro substituição). It is like this: You declare a variable called 'x' char(4) and another called 'y' char(3). you set value like 'DBO.' to x and (imagine the table name like 'OKK' than the 'y' will be set like 'OKK'.) The String will be 'DBO.OKK', but if you type the sintax 'SELECT * FROM @X+@Y' the sql server 2000 will not execute because i am trying to select some values of a String, not a table. In visual foxpro, for exemple, i should use in this case something like this [&] character to say to the compiler that is not a String. Is there someway to do it in SQL Server 2000?
    This is most usefull when working will multiples owners like i am doing.
    Second:
    Is there any sys command in SQL Server 2000 to list all owners of a single database?
    Thanks for any help!
  2. FrankKalis Moderator

    There is no such way in SQL Server. What comes closest is EXEC @sqlstring or EXEC sp_ExecuteSQL @sqlstring.
  3. suniljk7 New Member

    you have to create a dynamic sql after concatination of both the variables
    select @x='dbo', @y='okk'
    select @sqlstr='select * from '+@x+'.'+@y
    You can get db users from sysusers table in each database.
    Thanks
    John
  4. sunda New Member

    hi, sorry for delay. i was out for some time...
    this solution is great! but i was the point i stoped. how do i execute a variable string in sql in a way it can understand it like a command?
    for example:
    @string = 'select * from dbo.okk'
    how do i do? for exemple:
    exec @string (dont work)
    i have no ideas. Please help me!!!
    thanks,
  5. Adriaan New Member

    All you need is a pair of brackets:
    EXEC (@string)
    Also, to assign a value to a variable, use SET:
    SET @string = 'select * from dbo.okk'
  6. sunda New Member

    ok, u are amazing!
    thanks!

Share This Page