Dowt in create View

I want to create a view from dynamic tables

Query:

CREATE VIEW dbo.TrialView
AS
SELECT *
FROM (select name from dbo.sysobjects where name like 'scTabForm%' and type='u') p

Output:
Name
scTabForm0
scTabForm1

But I want all the fields from table starts with 'scTabForm'

like: select * from scTabForm0,scTabForm1,...

Regards
RaabuI think I know what you are getting at, but could you please clarify for me just a little bit? You have an unknown number of tables, with unknown structure. You want to create a view that will combine these and rename the columns to append the table they came from?

Double Quotes!

What is the normal procedure for enclosing string variables in Dynamic TSQL.
I've tried using:
SET QUOTED_IDENTIFIER ON
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = "o" ' + //problem here!!!
Cheers,
Adam'qtype = ''o'''
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eSkxjO8xFHA.2008@.TK2MSFTNGP10.phx.gbl...
> What is the normal procedure for enclosing string variables in Dynamic
> TSQL.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
>|||Use 2 single-quotes when one is desired in the resultant string. For
example:
SET QUOTED_IDENTIFIER ON
declare @.sqlquery varchar(1000)
declare @.oqs int
SET @.oqs = 0
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = ''o'' '
SELECT @.sqlquery
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eSkxjO8xFHA.2008@.TK2MSFTNGP10.phx.gbl...
> What is the normal procedure for enclosing string variables in Dynamic
> TSQL.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
>|||On Mon, 3 Oct 2005 12:39:00 +1000, Adam Knight wrote:

> What is the normal procedure for enclosing string variables in Dynamic TSQ
L.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
Use two single-quotes instead of the double-quotes.
Like
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = ''o'' '
Ayyappan Nair