Create a dynamic script to create view for all tables having MAX columns

  • Hi

    I need some help on this request

    Create a dynamic script to create view for all tables having MAX columns in the database

    And if the column is MAX then I need to use CONVERT(VARCHAR(2000), SUBSTRING (COLUMN_NAME,1,2000)) as COLUMN_NAME in the view

    Thanks

     

     

     

  • And what would you want to name the view?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Here's a rough go at it. It seems to work in my environment, but any really wide table will be a problem for PRINT. I would not blindly run a script like this. Assuming the number of tables is manageable I would verify the select for each one before creating the view.  This does not guarantee the columns will be ordered by ORDINAL_POSITION. I'm know if can be done, but I don't remember how. I think CHARACTER_MAXIMUM_LENGTH = -1 means MAX, but I would verify.

    SET NOCOUNT ON
    DECLARE @Total INT,
    @Counter INT = 1,
    @TableName VARCHAR(1000),
    @TableSchema VARCHAR(20),
    @SQLString1 VARCHAR(1000),
    @SQLString2 VARCHAR(MAX),
    @SQLString3 VARCHAR(MAX)

    DROP TABLE IF EXISTS #Tables
    CREATE TABLE #Tables (RecordID INT IDENTITY(1,1),
    TableSchema VARCHAR(500),
    TableName VARCHAR(50))

    INSERT #Tables (TableSchema,TableName )
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE = 'VARCHAR'
    AND CHARACTER_MAXIMUM_LENGTH = -1
    INTERSECT
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    SET @Total = @@ROWCOUNT
    WHILE @Counter <= @Total

    BEGIN

    SELECT @TableName = TableName,
    @TableSchema = TableSchema
    FROM #Tables
    WHERE RecordID = @Counter

    SET @SQLString1 = CONCAT('CREATE VIEW ', @TableSchema, '.uv',@TableName, CHAR(13), 'AS', CHAR(13), 'SELECT ')

    SELECT @SQLString2 = STRING_AGG(ColName,', ')
    FROM (
    SELECT
    CASE WHEN DATA_TYPE = 'VARCHAR'
    AND CHARACTER_MAXIMUM_LENGTH = -1
    THEN CONCAT('CAST(LEFT(',CONCAT('[',COLUMN_NAME,']'),',2000) AS VARCHAR(2000)) AS [',COLUMN_NAME,']')
    ELSE CONCAT('[',COLUMN_NAME,']')
    END AS Colname, ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    AND TABLE_SCHEMA = @TableSchema
    ) AS a

    SET @SQLString3 = CONCAT(@SQLString1, @SQLString2, CHAR(13), 'FROM ', @TableSchema, '.',@TableName, CHAR(13), 'GO', CHAR(13), '-----------')

    PRINT @SQLString3

    SET @Counter +=1

    END
  • Except stay completely away from INFORMATION_SCHEMA views, which are more overhead anyway.  Use sys.tables, sys.columns, etc., instead.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you very much Scott and Ed.

    First I trying to get the output and change to sys objects to see the difference.

    Some of the tables are having more than 200 columns ... I have to change the following statement to max type to get all the columns....

    But still some of the columns are missing ..

    Please suggest..

    SELECT @SQLString2 = STRING_AGG(CAST(ColName AS VARCHAR(MAX)),', ')

     

     

     

  • I generated SQL separately for each view, even though that requires a cursor.  In this specific case, I think you may want to be able to control things table by table.   By default, the code just PRINTs the sql; when ready, set @exec_sql = 1 to actually run the sql and create the view(s).

    --USE [your_db_name_goes_here]
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;

    SET NOCOUNT ON;

    DECLARE @column_name varchar(128);
    DECLARE @exec_sql bit;
    DECLARE @line_break varchar(10);
    DECLARE @print_sql bit;
    DECLARE @schema_name varchar(128);
    DECLARE @table_id int;
    DECLARE @table_name varchar(128);
    DECLARE @view_code varchar(max);

    SET @exec_sql = 0;
    SET @print_sql = 1;
    SET @line_break = CHAR(13) + CHAR(10);

    IF @exec_sql = 0
    SET @print_sql = 1;

    DECLARE cursor_tables CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, t.object_id
    FROM sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.max_length = -1
    ORDER BY 1, 2

    OPEN cursor_tables;
    WHILE 1 = 1
    BEGIN
    FETCH NEXT FROM cursor_tables INTO @schema_name, @table_name, @table_id;
    IF @@FETCH_STATUS <> 0
    BREAK;

    SELECT @view_code = STUFF((
    SELECT ', ' + CASE WHEN max_length = -1 THEN 'CAST(' + column_name + ' AS varchar(2000)) AS ' + column_name ELSE column_name END
    FROM sys.columns
    CROSS APPLY (
    SELECT '[' + name + ']' AS column_name
    ) AS ca1
    WHERE object_id = @table_id
    ORDER BY column_id
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
    , 1, 2, '')

    SET @view_code = N'CREATE VIEW [' + @schema_name + '].[' + @table_name + '_view] ' + @line_break +
    'AS ' + @line_break +
    'SELECT ' + @view_code + ' FROM [' + @schema_name + '].[' + @table_name + ']; ' + @line_break +
    'GO'

    IF @print_sql = 1
    PRINT @view_code;
    IF @exec_sql = 1
    EXEC(@view_code);
    END /*WHILE*/CLOSE cursor_tables;
    DEALLOCATE cursor_tables;

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thanks and appreciate your help Scott.

    Added the drop command before the CREATE view command..

    Even though the view_code variable declared as MAX it stores only 8000 characters...

    Some view commands doesn't include all columns.

    There are some tables the column names and data type string has more than 8000 characters..

    DROP VIEW [dbo].[AAA_epc_ssy_bu_VW]

    GO

    CREATE VIEW [dbo].[AAA_epc_ssy_bu_VW]

    AS

    SELECT [STUDENT_SCHOOL_YEAR_GU], [STUDENT_GU], [ORGANIZATION_YEAR_GU], [YEAR_GU], [REPORTED_TO_STATE_NOSHOW], [REPORTED_TO_STATE_LEAVE], [OLD_SIS_STUDENT_NUM], [GRADE], [GRADE_EXIT_CODE], [USER_CODE1], [USER_CODE2], [USER_CODE3], [USER_CODE4], [USER_CODE5], [USER_CODE6], [USER_CODE7], [USER_CODE8], [USER_CODE9], [ENTER_DATE], [ENTER_CODE], [LEAVE_DATE], [LEAVE_CODE], [HOMEROOM_SECTION_GU], [LAST_HOMEROOM_SECTION_GU], [INSTRUCTIONAL_SETTING], [COUNSELOR_SCHOOL_YEAR_GU], [LOCKER_NUMBER], [TRACK_GU], [PROGRAM_CODE], [EXTEND_LEARNING_PROGRAM], [SPECIAL_ENROLLMENT_CODE], [ACCESS_504], [SPECIAL_PROGRAM_CODE], [VOCATIONAL], [IVEP], [TECH_PREP], [YEAR_END_STATUS], [BUS_ROUTE_TO_SCHOOL], [BUS_ROUTE_FROM_SCHOOL], [TUTION_PAYER_CODE], [NEXT_SCHOOL_GU], [NEXT_GRADE_LEVEL], [HAS_CHANGED_FLAG], [STATUS], [HOMEBOUND], [DISTRICT_OF_RESIDENCE], [FTE], [SUMMER_WITHDRAWL_CODE], [SUMMER_WITHDRAWL_DATE], [TITLE_1_PROGRAM], [TITLE_1_SERVICE], [TITLE_1_EXIT], [LAST_ACTIVITY_DATE], [LAST_SCHOOL_GU], [EXCLUDE_ADA_ADM], [ALLOW_TYLENOL], [ALLOW_MEDICATION], [ATTEND_PERMIT_CODE], [ATTEND_PERMIT_DATE], [SCHOOL_RESIDENCE_GU], [NEXT_TRACK_GU], [FORBID_AUTO_DIAL], [NO_SHOW_STUDENT], [SCHED_HOUSE], [SCHED_TEAM], [SCHED_EXEMPT_HOUSE], [SCHED_EXEMPT_TEAM], [SCHED_LOW_PERIOD], [SCHED_HIGH_PERIOD], [SCHED_BALANCE], [SCHED_STAMP], [WITHDRAWAL_REASON_CODE], [DENY_PHOTO_INTERVIEW], [ABS_REPORT_POLICY], [STATEMENT_OF_AWARENESS], [REGISTRATION_RECEIVED], [REGISTRATION_UPDATED], [CAME_FROM], [MOVED_TO], [ENR_USER_1], [ENR_USER_2], [ENR_USER_3], [ENR_USER_DD_4], [ENR_USER_DD_5], [ENR_USER_DD_6], [ENR_USER_NUM_1], [ENR_USER_NUM_2], [ENR_USER_NUM_3], [ENR_USER_NUM_4], [ENR_USER_NUM_5], [ENR_USER_CHECK_1], [ENR_USER_CHECK_2], [ENR_USER_CHECK_3], [USER_NUM1], [USER_NUM2], [USER_NUM3], [USER_NUM4], [USER_NUM5], [USER_NUM6], [USER_NUM7], [USER_NUM8], [USER_DATE1], [USER_DATE2], [USER_DATE3], [USER_DATE4], [USER_CHECK1], [USER_CHECK2], [USER_CHECK3], [USER_CHECK4], [USER_CHECK5], [USER_CHECK6], [USER_CHECK7], [USER_CHECK8], [TRANSPORTATING_DISTRICT], [PXP_OCR_LOCKED_IN], [PXP_OCR_LOCKED_IN_DT], [PXP_OCR_VALIDATED], [PXP_OCR_VALIDATED_DT], [RECEIVER_SCHOOL], [SPEC_ED_SCH_OF_ATT], [OVERRIDE_FORCE_STS], [CAHSEE_ELA_RETAKE], [CAHSEE_MATH_RETAKE], [SUMMER_SCHOOL_GU], [SUMMER_GRADE_LEVEL], [DISTRICT_SPED_ACCOUNT], [ENR_USER_DD_1], [ENR_USER_DD_2], [ENR_USER_DD_3], [PICK_UP_TRANSPORT_TYPE], [PICK_UP_BUS_STOP], [PICK_UP_TRANSPORT_TIME], CAST([PICK_UP_COMMENT] AS varchar(2000)) AS [PICK_UP_COMMENT], [PICK_UP_LOCATION_TYPE], [PICK_UP_ADDRESS_GU], [DROP_OFF_TRANSPORT_TYPE], [DROP_OFF_BUS_STOP], [DROP_OFF_TRANSPORT_TIME], CAST([DROP_OFF_COMMENT] AS varchar(2000)) AS [DROP_OFF_COMMENT], [DROP_OFF_LOCATION_TYPE], [DROP_OFF_ADDRESS_GU], CAST([SPEC_TRANS_REQ_COMMENT] AS varchar(2000)) AS [SPEC_TRANS_REQ_COMMENT], [TRANSPORT_ELIGIBLE], [PICK_UP_SCHOOL_GU], [DROP_OFF_SCHOOL_GU], [COMPLETION_STATUS], [PICK_UP_TRANS_REAS_CODE], [PICK_UP_TRANS_REAS_DATE], [DROP_OFF_TRANS_REAS_CODE], [DROP_OFF_TRANS_REAS_DATE], [CHANGE_DATE_TIME_STAMP], [CHANGE_ID_STAMP], [ADD_DATE_TIME_STAMP], [ADD_ID_STAMP], [EXP_CODE], [EXP_TIME_CODE], [CB_LAST_REQUEST_UPDATE], [RESPON_DISTRICT], [RESPON_SCHOOL], [SERV_DISTRICT], [SERV_SCHOOL], [SCHOOL_CHOICE_STATUS], [FULL_TIME_VIRTUAL], [LEAVE_UNATTENDED], [SCH_START_TIME], [SCH_DISMISS_TIME], [PICK_UP_RESP_PERSON], [PICK_UP_RESP_PHONE], [DROP_OFF_RESP_PERSON], [DROP_OFF_RESP_PHONE], [SPCL_PRGM_TCH_SCH_YR_GU], [TRANSPORT_REQUEST_DATE], [TRANSPORT_START_DATE], [ENTERED_BY_USER], [NEXT_SCH_ATTEND], [SPED_1ST_SEM_REIMBRSMNT], [SPED_2ND_SEM_REIMBRSMNT], [TRUANCY_CONFERENCE], [SR_USER_CODE_DD_01], [SR_USER_CODE_DD_02], [SR_USER_CODE_DD_03], [SR_USER_CODE_DD_04], [SR_USER_CODE_DD_05], [SR_USER_CODE_DD_06], [SR_USER_CODE_DD_07], [SR_USER_CODE_DD_08], [SR_USER_CODE_DD_09], [SR_USER_CODE_DD_10], [SR_USER_CODE_DD_11], [SR_USER_CODE_DD_12], [SR_USER_CODE_DD_13], [SR_USER_CODE_DD_14], [SR_USER_CODE_DD_15], [SR_USER_CODE_DD_16], [SR_USER_CODE_DD_17], [SR_USER_CODE_DD_18], [SR_USER_CODE_DD_19], [SR_USER_CODE_DD_20], [SR_USER_CHECK_01], [SR_USER_CHECK_02], [SR_USER_CHECK_03], [SR_USER_CHECK_04], [SR_USER_CHECK_05], [SR_ENR_USER_NUM_01], [SR_ENR_USER_NUM_02], [SR_ENR_USER_NUM_03], [SR_ENR_USER_NUM_04], [SR_ENR_USER_NUM_05], [SR_ENR_USER_DD_01], [SR_ENR_USER_DD_02], [SR_ENR_USER_DD_03], [SR_ENR_USER_DD_04], [SR_ENR_USER_DD_05], [SR_ENR_USER_DD_06], [SR_ENR_USER_DD_07], [SR_ENR_USER_DD_08], [SR_ENR_USER_DD_09], [SR_ENR_USER_DD_10], [SR_ENR_USER_DD_11], [SR_ENR_USER_DD_12], [SR_ENR_USER_DD_13], [SR_ENR_USER_DD_14], [SR_ENR_USER_DD_15], [SR_ENR_USER_DD_16], [SR_ENR_USER_DD_17], [SR_ENR_USER_DD_18], [SR_ENR_USER_DD_19], [SR_ENR_USER_DD_20], [SR_ENR_USER_CHECK_01], [SR_ENR_USER_CHECK_02], [SR_ENR_USER_CHECK_03], [SR_ENR_USER_CHECK_04], [SR_ENR_USER_CHECK_05], [SR_ENR_USER_CHECK_06], [SR_ENR_USER_CHECK_07], [SR_ENR_USER_CHECK_08], [SR_ENR_USER_CHECK_09], [SR_ENR_USER_CHECK_10], [OPT_OUT_MEDICAL_STATE], [OPT_OUT_MEDICAL_FED], [SCH_COMPL_CODE], [SPED_REIMBRSMNT_DSBLT_CD], [SPED_SUM_SEM_REIMBRSMNT], [SR_ENR_TEXT_1], [SR_ENR_TEXT_2], [SR_ENR_TEXT_3], [SR_ENR_TEXT_4], [SR_ENR_TEXT_5], [SR_ENR_TEXT_6], [SR_ENR_TEXT_7], [SR_ENR_TEXT_8], [SR_ENR_TEXT_9], [SR_ENR_TEXT_10], [SR_USER_DATE_1], [SR_USER_DATE_2], [SR_USER_DATE_3], [SR_USER_DATE_4], [SR_USER_DATE_5], [SR_USER_NUM_1], [SR_USER_NUM_2], [SR_USER_NUM_3], [SR_USER_NUM_4], [SR_USER_NUM_5], [SR_USER_TEXT_1], [SR_USER_TEXT_2], [SR_USER_TEXT_3], [SR_USER_TEXT_4], [SR_USER_TEXT_5], [SR_USER_TEXT_6], [SR_USER_TEXT_7], [SR_USER_TEXT_8], [SR_USER_TEXT_9], [SR_USER_TEXT_10], [MILITARY_COMPACT_STATUTE], [SUB_SCHOOL], [COLLEGE_ENROLLED], [PREVIOUS_LOCATION_TYPE], [PREVIOUS_YEAR_END_STATUS], [STU_CTDS_NUMBER], [REPORTING_SCHOOL], [COUNTY_RESIDENT], [COUNTY_RESIDENT_NEW], [PREV_IN_GRADE], [NON_RESIDENT], [CONTINUOUS_STATE], [CONTINUOUS_SCHOOL], [CONTINUOUS_DISTRICT], [STATE_FUNDING_STATUS], [STU_INSTRUCTIONAL_HOURS], [SR_ENR_USER_DATE_01], [SR_ENR_USER_DATE_02], [SR_ENR_USER_DATE_03], [SR_ENR_USER_DATE_04], [SR_ENR_USER_DATE_05], [PUPIL_ATT_INFO], [ALLOW_IBUPROFEN], [INIT_NINTH_GRADE_YEAR], [ENR_PATHWAY], [NEXT_ENR_PATHWAY], [NYR_EXCLUDE], [BUS_ESTIMATED_MILEAGE], [COURSE_OF_STUDY], [DROP_OFF_BUS_NUMBER], [PICK_UP_BUS_NUMBER], [STANDARD_DAY_MINUTES], [HOME_SCHOOLED], [EMPLOYED_WHILE_ENROLLED], [MILITARY_CONNECTION], [PK_FUNDING_SOURCE], [MANUAL_FTE_OVERRIDE], [OVERRIDE_CONT_AND_FUNDING], [SMR_WITHDRAWAL_REASON_CODE], [SR_USER_CODE_DD_21], [SR_USER_CODE_DD_22], [ADMINISTRATOR_SCH_YR_GU], [UNACCOMPANIED_YOUTH], [REFUGEE], [NIGHTTIME_RESIDENCE], [IMMIGRANT], [FOSTER_HOME], [ALT_EDU_STU], [RESIDENT_TOWN], [RESIDENT_DISTRICT], [EXC_FRM_AUTO_CALC_FTE], [DRP_PARTICIPANT], [ATT_PLAN_CODE], [ATT_CONF_CODE], [COURT_REFERRAL_CODE], [STATE_GRADE_LVL_OVERRIDE], [STATE_ENROLLMENT_TYPE], [HAZARDOUS_WALKING_CODE], [PRV_WITHDRAWAL_CDE], [CRNT_ENTER_CDE], [SR_USER_CODE_DD_23], [SR_USER_CODE_DD_24], [SR_ENR_USER_DD_23], [SR_ENR_USER_DD_24], [RECEIVER_DISTRICT], CAST([NO_SHOW_COMMENT] AS varchar(2000)) AS [NO_SHOW_COMMENT], [NO_SHOW_RECEIVER_DISTRICT], [NO_SHOW_RECEIVER_SCHOOL], CAST([INACTIVATE_COMMENT] AS varchar(2000)) AS [INACTIVATE_COMMENT], [NO_SHOW_DATE], [SCHED_WEIGHT], [DISC_DET_HOURS], [DISC_DET_HOURS_SERVED], [SCHED_PATHWAY_HOUSE], [SCHED_PATHWAY_TEAM], [PRIOR_SCHOOL_DISTRICT], [PRIOR_SCHOOL_STATE], [PRIOR_SCHOOL_COUNTRY], [STUDENT_OFFENDER_TRANSFER], [EDUCATIONAL_CHOICE], [DISASTER_AFFECTED_STUDENT], [PICK_UP_VEHICLE_CAT], [DROP_OFF_VEHICLE_CAT], [TRANSPORT_MEMBERSHIP_CAT], [SR_ENR_USER_CHECK_11], [SR_ENR_USER_CHECK_12], [SR_ENR_USER_CHECK_13], [SR_ENR_USER_CHECK_14], [SR_ENR_USER_CHECK_15], [INTNSV_SUPP_SVC_SEM1], [INTNSV_SUPP_SVC_SEM2], [INTNSV_SUPP_SVC_SUM], [EL_CASE_MGR_GU], [EL_CASE_MGR_ASSN_DATE], [SCHD_STATUS], [INTNSV_SUPP_SVC_BGN], [NEXT_ATTEND_PERMIT_CODE], [NEXT_ATTEND_PERMIT_DATE], [NEXT_CONCURRENT_SCHOOL_GU], [SR_USER_NUM_EXT_1], [RESIDENT_STATUS], [CAREER_ACADEMY_1], [CAREER_ACADEMY_2], [CAREER_

    DROP VIEW [dbo].[AAA_epc_stu_bu_VW]

    GO

    ....

  • Did you try executing the code as well as printing? Print can only return 8k so it will truncate the variable if it's longer, but you can execute dynamic sql strings that are longer than 8k. Try returning LEN(@view_code) to see if it really is truncated.

    I would limit the cursor to one table that you know has too many columns to print, set @exec_sql = 1 and run it. If it works, look at the view definition.

    If you need a script to look at, you could define two more variables, set the first to left(@view_code,8000) and the other to something like right(@view_code,(len(@view_code)-7999)) then print them both (assuming you don't have any >16k).

  • Thanks Ed

    Adding left and right variable and print shows the full string info

    Saved that script as a.sql and I would like to run it using sqlcmd by passing it

    sqlcmd -S SYNSQL101\DEMOSYN -i a_wrapper.sql -o C:\PGCPS_SQLServer_Scripts\scripts\a.sql

    it fails with this message

    Msg 102, Level 15, State 1, Server SYNSQL101\DEMOSYN, Line 2

    Incorrect syntax near 'GO'.

    Msg 111, Level 15, State 1, Server SYNSQL101\DEMOSYN, Line 4

    'CREATE VIEW' must be the first statement in a query batch.

    Msg 102, Level 15, State 1, Server SYNSQL101\DEMOSYN, Line 7

    Incorrect syntax near 'GO'.

     

     

     

     

     

    Attachments:
    You must be logged in to view attached files.
  • Hi Ed

    Even running it directly on MSSM tool, it gives the error.

    Please help on this issue.

    CREATE VIEW [PGCPS_CUSTOM].[EGB_CC_SEQ_STANDARDS_VW]

    AS

    SELECT [ID], [CC_SEQ_ID], [STANDARD_ID], [SEQ], CAST([CONTENT] AS varchar(2000)) AS [CONTENT], [INSTRUCTION_DAYS], [START_DAY_NUM] FROM [rev].[EGB_CC_SEQ_STANDARDS];

    GO

    Msg 102, Level 15, State 1, Procedure EGB_CC_SEQ_STANDARDS_VW, Line 4 [Batch Start Line 2]

    Incorrect syntax near 'GO'.

    CREATE VIEW [PGCPS_CUSTOM].[EGB_CC_SETTINGS_VW]

    AS

    SELECT [ID], [CLASSID], CAST([GROUP_OBJ] AS varchar(2000)) AS [GROUP_OBJ], CAST([CONTACT_TEXT] AS varchar(2000)) AS [CONTACT_TEXT], CAST([COURSE_TEXT] AS varchar(2000)) AS [COURSE_TEXT], CAST([GRADING_TEXT] AS varchar(2000)) AS [GRADING_TEXT], CAST([POLICIES_TEXT] AS varchar(2000)) AS [POLICIES_TEXT], CAST([GB_CONFIGURATION] AS varchar(2000)) AS [GB_CONFIGURATION] FROM [rev].[EGB_CC_SETTINGS];

    GO

    Msg 102, Level 15, State 1, Procedure EGB_CC_SETTINGS_VW, Line 4 [Batch Start Line 2]

    Incorrect syntax near 'GO'.

  • Long shot: Have you changed the batch separator to something different than the default? That would produce the error you describe.

    In SSMS check

    Tools-> Options -> Query Execution -> General

    and

    Query... -> Execution --> General

  • I found that a simple example generates the error above if you use CHAR(13) alone as a line break (which was in my suggested solution). Scott's solution used CHAR(13) + CHAR(10). Which did you use for the new line?

    -- CHAR(13) alone fails
    DECLARE @sql varchar(1000),
    @linebreak VARCHAR(12)= CHAR(13)
    SET @sql = concat('CREATE VIEW [dbo].[v1] AS SELECT 1 AS Col1', @linebreak, 'GO', @linebreak,'CREATE VIEW [dbo].[v2] AS SELECT 2 AS Col1',@linebreak, 'GO')

    PRINT @sql

    -- CHAR(13) + CHAR(10) succeeds
    DECLARE @sql varchar(1000),
    @linebreak VARCHAR(12)= CHAR(13)+CHAR(10)
    SET @sql = concat('CREATE VIEW [dbo].[v1] AS SELECT 1 AS Col1', @linebreak, 'GO', @linebreak,'CREATE VIEW [dbo].[v2] AS SELECT 2 AS Col1',@linebreak, 'GO')

    PRINT @sql

  • Hi Ed

    Thanks for the reply

    I use the same suggested line break

    Please find attached script I am using it.

     

    Attachments:
    You must be logged in to view attached files.
  • Just remove the GO - there's no need for it.

    SET @view_code = N'CREATE VIEW [' + 'PGCPS_CUSTOM' + '].[' + @table_name + '_VW] ' + @line_break +

    'AS ' + @line_break +

    'SELECT ' + @view_code + ' FROM [' + @schema_name + '].[' + @table_name + ']; '

    IF @exec_sql = 1

    EXEC(@view_code);

    The EXEC() function will produce the error if there's a GO in the variable it executes.

    You can easily try it yourself.

     

  • CREATE VIEW must be in a batch by itself, i.e., without GOs, you could create only one view per script.

    Since GOs are likely not valid, that may be what you have to do.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply