Question

I print out a bunch of DDL statements that are dynamically created and want to align the output in a specific way.

PRINT  ALTER TABLE   + @TableName +   WITH NOCHECK ADD CONSTRAINT CK_  + @TableName +  _  + @ColumnName +  _MinimumLength CHECK (LEN(  + @ColumnName +  ) > 0)

Output:

ALTER TABLE SignType ADD CONSTRAINT CK_SignType_Description_MinimumLength CHECK (LEN(Description) > 0)
ALTER TABLE Person ADD CONSTRAINT CK_Person_Name_MinimumLength CHECK (LEN(Name) > 0)

What I want the output to be:

ALTER TABLE SignType                WITH NOCHECK ADD CONSTRAINT CK_SignType_Description_MinimumLength                CHECK (LEN(Description) > 0)
ALTER TABLE Person                  WITH NOCHECK ADD CONSTRAINT CK_Person_Name_MinimumLength                         CHECK (LEN(Name) > 0)

Is there a function that allows me to pad the string by n of character x. I would use it like this:

PRINT  ALTER TABLE   + @TableName + PAD(   , 50 - LEN(@TableName)) +   WITH NOCHECK ADD CONSTRAINT .....

Thanks

Answer 1

I believe what you want is the REPLICATE function available from SQL 2005 on.

PRINT  ALTER TABLE   + @TableName + REPLICATE(   , 50 - LEN(@TableName)) +   WITH NOCHECK ADD CONSTRAINT .....

MSDN: REPLICATE

Answer 2

You can use the SPACE function:

PRINT  ALTER TABLE   + @TableName + SPACE( 50 - LEN(@TableName)) +   WITH NOCHECK ADD CONSTRAINT .....

If it s something else than a space you want to insert, you can use REPLICATE(varchar, int).

Answer 3

You need to use the REPLICATE function, along with DATALENGTH:

PRINT  ALTER TABLE   + @TableName + REPLICATE(   , 50 - DATALENGTH(@tablename)) +   WITH NOCHECK ADD CONSTRAINT .....

友情链接