asked on

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?

Zberteoc

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.

Zberteoc

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

Zberteoc

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

rwheeler23

ASKER

Nice scripts. Thanks.

Zberteoc

Be careful, though, the script doesn't deal with the identity property.

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

tHANKS