Home Forums Article Discussions Article Discussions by Author Discuss content posted by Lowell Get DDL for any SQL 2005 table RE: Get DDL for any SQL 2005 table

  • October 18, 2011 at 7:43 am

    #1395937

    kbleeker (10/18/2011)

    Hey again.

    I saw in the SP that you insert the data into @Results, so I caught the contents of that - check out the index_name and index_columns_key columns(only 2 rows, since only one index):

    index_name -

    ---

    CODE

    index_columns_key-

    ---

    CODE,

    And there's the magical comma - let me state in no unceratin terms that I'm no SQL ninja, I'm very new to this. It looks to me though that my index columns are being stored somewhere in sys.objects with the commas attached. Does this bring any ideas to mind?

    yeah, that's part of the concatenation of all possible columns...it gets built with FOR XML PATH('') , but later in the query wrapped with this:

    SELECT

    LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,

    which takes off the extra comma;

    what i suspect is that specific table may have a column named --notice the space! that might be throwing the scripting off. i'm testing that scenarion now..i might need to quotename() each of the columns in the index to fix it...fast fix if that's the isssue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!