-1

I have a query that i am writing to convert some heap tables with primary keys into clustered tables, but the string that i'm generating truncates on objects past a certain length it seems.

SELECT 
    --t.name,
 --   t.schema_id,
 --   t.type,
 --   t.type_desc,
 --   i.name,
 --   i.type,
 --   i.type_desc,
 --   i.is_primary_key,
    --c.name,
    'print ''dropping index on dbo.' + CAST(t.name AS VARCHAR(MAX)) + ''';' + CAST(CHAR(10) AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX))  + 
    'drop index ' + CAST(i.name AS VARCHAR(MAX)) + ' on dbo.' + CAST(t.name AS VARCHAR(MAX)) + ';' + CAST(CHAR(10) AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX)) +
    'go' + CAST(CHAR(10) AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX)) +
    'print ''creating clustered primary key on dbo.' + CAST(t.name AS VARCHAR(MAX)) + ''';' + CAST(CHAR(10) AS VARCHAR(MAX)) + CAST(CHAR(13) AS VARCHAR(MAX)) +
    'alter table dbo.' + CAST(t.name AS VARCHAR(MAX)) + ' add constraint ' + CAST(i.name AS VARCHAR(MAX)) + ' primary key clustered (' + CAST(c.name AS VARCHAR(MAX)) + ');'
FROM sys.tables t
    INNER JOIN sys.indexes i
        ON i.object_id = t.object_id
    INNER JOIN sys.index_columns ic
        ON i.object_id = ic.object_id
            AND i.index_id = ic.index_id
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
            AND ic.index_column_id = c.column_id
WHERE i.type = 2
    AND t.object_id NOT IN
(
    SELECT distinct t.object_id AS tableid
    FROM sys.tables t
        INNER JOIN sys.indexes i
            ON t.object_id = i.object_id
    WHERE i.type  = 1
)
ORDER BY t.name,
    i.index_id

As you can see i tried wrapping all of the columns that i'm using in a cast statement, but it hasn't helped. I'm not really sure why this isn't working.

example of the complete code:

print 'dropping index on dbo.Account';

drop index PK_Account on dbo.Account;

go

print 'creating clustered primary key on dbo.Account';

alter table dbo.Account add constraint PK_Account primary key clustered (Id);

example of the truncated code:

drop index PK_ALongerTableNameABC on dbo.ALongerTableNameABC;

go

print 'creating clustered primary key on dbo. ALongerTableNameABC';

alter table dbo.ALongerTableNameABC add constraint PK_ALongerTableNa

I am grabbing the generated code by running the query with Results to Text.

2
  • Have you tried casting the entire resulting column to varchar(max), instead of doing that to its constituent parts (for no apparent reason)? – mustaccio Oct 7 '20 at 20:19
  • 1
    Results to text can be 8,192 characters. If that's not enough, see this tip. – Aaron Bertrand Oct 7 '20 at 20:50
1

The reason for this is that the default Results to Text option's maximum number of characters displayed is set to 256 characters. That's why the rest of the text output is truncated.

Try results to Grid, and you should be a happy camper :-)

2
  • 1
    well, yeah that fixed it, but now i don't get my new lines – DForck42 Oct 7 '20 at 20:23
  • 2
    Results to grid also has a limit, and unless you change an option it puts everything on one line (typically it is not a very useful way to examine output like that in the question - CR/LF are important). – Aaron Bertrand Oct 7 '20 at 20:51
0

So I found two ways to fix this, with @FlogDonkey sending me down the right path.

There is an option for Results to Text for the max characters displayed, i overwrite that from 256 to 1000 and it worked.

I also found that in Results to Grid you can set it to Retain CR/LF on copy or save.

I did, however, have to restart SSMS to get these changes to take effect.

2
  • 2
    These changes do not require a full restart of SSMS, but rather simply opening a new query window. These settings are applied to a query window at the time it is opened, so already-opened query windows will not notice the setting change – AMtwo Oct 7 '20 at 23:36
  • @AMtwo ahh i didn't know that, thank you! – DForck42 Oct 9 '20 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.