add tempdb files for each CPUs

set nocount on

declare @cpu smallint
declare @num_tempdb_files smallint
declare @product_version smallint
declare @tempdb_path nvarchar(1000)
declare @exist_tempdb_files smallint
set @product_version = @@microsoftversion / 0x01000000

-- count @cpu
if @product_version = 8 or @product_version = 9 or @product_version = 10 or @product_version = 11
begin
    print '-- 2000/2005/2008/2008R2/2012'
    create table #xp_msver (ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
    insert into #xp_msver exec master.dbo.xp_msver 'ProcessorCount'
    select @cpu = Internal_Value from #xp_msver
    drop table #xp_msver
end

-- set up @num_tempdb_files
if (@cpu <= 8)
begin
    set @num_tempdb_files = @cpu
end
else
begin
    set @num_tempdb_files = 8
    while @num_tempdb_files + 4 <= @cpu
    begin
        set @num_tempdb_files = @num_tempdb_files + 4
    end
end
print '-- needed tempdb data files:' + cast(@num_tempdb_files as varchar)
select @exist_tempdb_files = count(*) from tempdb.dbo.sysfiles where groupid <> 0
print '-- exist tempdb data files:' + cast(@exist_tempdb_files as varchar)

-- print modify sql
if @exist_tempdb_files >= @num_tempdb_files
begin
    print '-- already added. nothing to do.'
end
else
begin
    print '-- do add'
    select @tempdb_path = rtrim(replace(filename,'tempdb.mdf','')) from tempdb.dbo.sysfiles where fileid = 1
    print '-- file path:' + @tempdb_path
    print 'use [master]'
    print 'alter database [tempdb] modify file (name = N''tempdev'', size = 100, filegrowth = 100)'
    print 'alter database [tempdb] modify file (name = N''templog'', size = 100, filegrowth = 100)'
    while @exist_tempdb_files+1 <= @num_tempdb_files
    begin
        set @exist_tempdb_files = @exist_tempdb_files + 1
        print 'alter database [tempdb] add file (name = N''tempdev' + cast(@exist_tempdb_files as varchar) + ''', filename = N''' + @tempdb_path + 'tempdev' + cast(@exist_tempdb_files as varchar) + '.ndf'', size = 100, filegrowth = 100)'
    end
end