Need syntax help on using the sp_exec statement

Question

Post reply

Need syntax help on using the sp_exec statement

mw_sql_developer

SSCoach

Points: 19445
More actions
August 4, 2016 at 10:56 am

#328820

The following works fine, I run this , copy the output paste it in another window, hit F5 and I am done!
Wonder if someone can modify the code so that it can run the DDL statement as well ( Without me having to copy and paste )
Declare @SCHEMA VARCHAR(100);
Declare @TBL_PREFIX VARCHAR(100);
Select @SCHEMA = 'CLAIM'
Select @TBL_PREFIX = 'IMP'
Select
'ALTER TABLE ' + TABLE_SCHEMA + '.' + table_name + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST( CHARACTER_MAXIMUM_LENGTH as VARCHAR ) + ') NOT NULL; '
FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @SCHEMA and table_name like @TBL_PREFIX + '%'
and
COLUMN_NAME in ('SourceID', 'ClaimNumber', 'ClaimLineNumber','DataSourceType','FirstServiceDate','Denied','MemberID','ProductID','ProviderID','AsOfDate' )
AND
DATA_TYPE in ( 'VARCHAR' )
ORDER BY 1

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Assign it to a variable and then use sp_executesql

Here's another link: https://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Not sure since it produces more than one row as output.. Some help would be appreciated!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You could do it with the following method: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = ( Select 'ALTER TABLE ' + TABLE_SCHEMA + '.' + table_name

+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE

+ '(' + CAST( CHARACTER_MAXIMUM_LENGTH as VARCHAR ) + ') NOT NULL; '

FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_SCHEMA = @SCHEMA

and table_name like @TBL_PREFIX + '%'

and COLUMN_NAME in ('SourceID', 'ClaimNumber', 'ClaimLineNumber','DataSourceType','FirstServiceDate','Denied','MemberID','ProductID','ProviderID','AsOfDate' )

AND DATA_TYPE in ( 'VARCHAR' )

ORDER BY 1

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)')

PRINT @sql;

Or you could go "old school"

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'ALTER TABLE ' + TABLE_SCHEMA + '.' + table_name

+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE

+ '(' + CAST( CHARACTER_MAXIMUM_LENGTH as VARCHAR ) + ') NOT NULL; '

FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_SCHEMA = @SCHEMA

and table_name like @TBL_PREFIX + '%'

and COLUMN_NAME in ('SourceID', 'ClaimNumber', 'ClaimLineNumber','DataSourceType','FirstServiceDate','Denied','MemberID','ProductID','ProviderID','AsOfDate' )

AND DATA_TYPE in ( 'VARCHAR' );

PRINT @sql;

I prefer the first method since it's easier to modify and control.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Done! Works!

Thanks

Declare @SCHEMA VARCHAR(100);

Declare @TBL_PREFIX VARCHAR(100);

Select @SCHEMA = 'CLAIM'

Select @TBL_PREFIX = 'IMP'

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = ( Select 'ALTER TABLE ' + TABLE_SCHEMA + '.' + table_name

+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE

+ '(' + CAST( CHARACTER_MAXIMUM_LENGTH as VARCHAR ) + ') NULL; '

FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_SCHEMA = @SCHEMA

and table_name like @TBL_PREFIX + '%'

and COLUMN_NAME in ('ProviderID')

AND DATA_TYPE in ( 'VARCHAR' )

ORDER BY 1

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)');

--SELECT @sql;

EXECUTE sp_executesql @sql;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

mw112009 (8/4/2016)
Done! Works!
Thanks

Do you understand how it works? The article should explain it clearly, but you can post any questions after reading it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2