re-size tempdb file counts and sizes

USE master
GO
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @NUM_OF_FILES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT

SELECT @NUM_OF_FILES = 12 -- Number of tempdb files you want to have.
SELECT @SIZE = 10240      -- Size in MB
SELECT @GROWTH = 10      
SELECT @ISPERCENT = 1

PRINT @NUM_OF_FILES

SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH

SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')

WHILE @FILECOUNT > @NUM_OF_FILES
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    REMOVE FILE tempdev' + RTRIM(CAST(@FILECOUNT as CHAR))
    EXEC(@SQL_SCRIPT)
    PRINT 'Removed ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@FILECOUNT as CHAR)) + '.ndf'
    SET @FILECOUNT = @FILECOUNT - 1
END

WHILE @NUM_OF_FILES > @FILECOUNT
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    ADD FILE
    (
    FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'',
    NAME = tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + ',
    SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
    FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
    IF @ISPERCENT > 0
    SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
    SET @SQL_SCRIPT = @SQL_SCRIPT + ')'

    EXEC(@SQL_SCRIPT)
    PRINT 'Created ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'
    SET @NUM_OF_FILES = @NUM_OF_FILES - 1
END

WHILE @FILECOUNT > 0
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    MODIFY FILE ( NAME = tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + ',
    SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB ,
    FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
    IF @ISPERCENT > 0
    SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
    SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
    EXEC(@SQL_SCRIPT)
    PRINT 'Modified ' + @BASEPATH + 'tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + '.ndf'
    SET @FILECOUNT = @FILECOUNT - 1
END
GO