USE [EOICARD_old] --change DB
SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,
ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA,TABLE_NAME) [no]
into #tmp
FROM INFORMATION_SCHEMA.TABLES; --create temp table
DECLARE @tb_name nvarchar(100), @num int, @total int, @i int, --declare variable
@tb_schema nvarchar(100), @sql nvarchar(100);
DECLARE @tble_total table ( --declare output table
schema_ nvarchar(100),
name nvarchar(100),
rowtotal int
);
SELECT @num=count(*) FROM #tmp; --set total table count
SET @i=1; --set loop count
while @i<=@num --loop start
BEGIN
SELECT @tb_schema=TABLE_SCHEMA FROM #tmp; --get schema name
SET @tb_name=(SELECT TABLE_NAME FROM #tmp WHERE [no]=@i); --get table name
SET @sql=N'SELECT '''+@tb_schema+''','''+@tb_name+''',count(*) FROM '+ @tb_schema+'.'+@tb_name; --set sql statement
INSERT INTO @tble_total(schema_,name,rowtotal) EXEC(@sql) --temp table row count
SET @total = (select top (1) rowtotal from @tble_total WHERE name=@tb_name); --set row count
SET @i=@i+1; --set next loop
END --loop end
SELECT * FROM @tble_total; --output
DROP TABLE #tmp; --drop tmp table
GO
結果會大致長這樣，有schema、table name、總筆數
ALTER DATABASEdatabase_nameSales
{
<add_or_modify_files>::= --這邊是從下面複製上來的
{
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>::= --這邊也是從下面複製上來的
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED }
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
}
| <add_or_modify_filegroups>}[;]
- database_name=Sales
ALTER DATABASE Sales
{
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
|MODIFY FILE
<filespec>::= ( NAME = logical_file_name[ , NEWNAME = new_logical_name ][ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ][ , SIZE = size [ KB | MB | GB | TB ] ][ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ][ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ][ , OFFLINE ])
}
就變成
ALTER DATABASE SalesMODIFY FILE
<filespec>::= ( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
ALTER DATABASE Sales
MODIFY FILE
<filespec>::=
(
NAME =logical_file_nameSales_sec
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
變成
ALTER DATABASE Sales
MODIFY FILE (
NAME =Sales_sec
[ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
)
ALTER DATABASE Sales
MODIFY FILE (
NAME =Sales_sec
[, FILENAME ={'os_file_name'| 'filestream_path' | 'memory_optimized_data_path' } ]
)
所以變成
- os_file_name＝M:\data\ Sales_sec.ndf
ALTER DATABASE Sales
MODIFY FILE (
NAME =Sales_sec
, FILENAME = 'M:\data\ Sales_sec.ndf'
)
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
