SSMS Auto Generate scripts

When you go into SQL 2012/2014 Management Studio and are looking at the list of the tables in a database and then on a table you right click and select Script Table as Insert as an example, if you have a table with hundreds of fields you will get a lengthy list of fields each one on its own row. Now since I am looking to take this script and paste it into a program, I would prefer to see fields group on a line with 7-8 fields per line. Is there a option setting that will allow that or some kind of conversion tool to format your SQL statement?
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
No. You will have to do it manually or to build a script yourself in order to get that. However, I don't think it worth the effort. The formatting is just a matter of make it easier to read and it doesn't really matter how many lines it will take.  Especially if you paste it in an application code in the end it will help to have that formatting.
0
ZberteocCommented:
Here is an example of code that will do that for you but only regarding the columns list:
declare
	@cols varchar(max)=''
select 
	@cols=@cols+
	COLUMN_NAME+' '+
	DATA_TYPE+' '+
	case when DATA_TYPE like '%CHAR' then '('+cast(CHARACTER_MAXIMUM_LENGTH as varchar)+')' else '' end +' '+
	case when DATA_TYPE in ('numeric', 'decimal') then '('+cast(NUMERIC_PRECISION as varchar)+','+cast(NUMERIC_SCALE as varchar)+')' else '' end +' '+
	case when COLUMN_DEFAULT is not null then COLUMN_DEFAULT
	else ''
	end+' '+
	case when IS_NULLABLE ='NO' then 'NOT NULL' else 'NULL' end + ' '+
	', '+case when ORDINAL_POSITION%7=0 then CHAR(13)+char(10) else '' end 

from 
	INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='YorTableName'
print @cols

Open in new window

0
ZberteocCommented:
Small correction:
declare
	@cols varchar(max)=''
select 
	@cols=@cols+
	COLUMN_NAME+' '+
	DATA_TYPE+' '+
	case when DATA_TYPE like '%CHAR' then '('+cast(CHARACTER_MAXIMUM_LENGTH as varchar)+')' else '' end +' '+
	case when DATA_TYPE in ('numeric', 'decimal') then '('+cast(NUMERIC_PRECISION as varchar)+','+cast(NUMERIC_SCALE as varchar)+')' else '' end +' '+
	case when COLUMN_DEFAULT is not null then 'DEFAULT '+cast(COLUMN_DEFAULT as varchar)
	else ''
	end+' '+
	case when IS_NULLABLE ='NO' then 'NOT NULL' else 'NULL' end + ' '+
	', '+case when ORDINAL_POSITION%7=0 then CHAR(13)+char(10) else '' end 

from 
	INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='YorTableName'
print @cols

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rwheeler23Author Commented:
Nice scripts. Thanks.
0
ZberteocCommented:
Be careful, though, the script doesn't deal with the identity property.
0
ZberteocCommented:
Here is with IDENTITY as well:
declare
	@cols varchar(max)=''
select 
	@cols=@cols+
	COLUMN_NAME+' '+
	DATA_TYPE+' '+
	case when COLUMNPROPERTY( OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity')=1 then 'IDENTITY' else '' end +' '+
	case when DATA_TYPE like '%CHAR' then '('+cast(CHARACTER_MAXIMUM_LENGTH as varchar)+')' else '' end +' '+
	case when DATA_TYPE in ('numeric', 'decimal') then '('+cast(NUMERIC_PRECISION as varchar)+','+cast(NUMERIC_SCALE as varchar)+')' else '' end +' '+
	case when COLUMN_DEFAULT is not null then 'DEFAULT '+cast(COLUMN_DEFAULT as varchar)
	else ''
	end+' '+
	case when IS_NULLABLE ='NO' then 'NOT NULL' else 'NULL' end + ' '+
	', '+case when ORDINAL_POSITION%7=0 then CHAR(13)+char(10) else CHAR(13)+char(10) end 

from 
	INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='YorTableName'
print @cols

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
tHANKS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.