自動產生資料表欄位的SQL

  • 221
  • 0

自動產生資料表欄位的SQL

select 
	'alter table [' + SCHEMA_NAME(schema_id) + '].[' + o.name +'] 
	add  [' + c.name + '] ' + TYPE_NAME(system_type_id) +  
	CASE WHEN system_type_id in (165,173,167,175,231,239)  THEN
	 CASE WHEN MAX_LENGTH > 8000 OR MAX_LENGTH = -1 THEN '(max)'
	 ELSE isnull('('+ CASE WHEN system_type_id in (231,239) THEN 
		 RTRIM(MAX_LENGTH/2) ELSE RTRIM(MAX_LENGTH) END +')', '')
	  END ELSE ''
	END
from sys.columns c join sys.objects o 
on c.object_id=o.object_id where o.type='U'