Lập Trình .NET

SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        PRINT N'Executing: ' + @command;  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO

SELECT a.index_id
,name
,avg_fragmentation_in_percent
,OBJECT_NAME(a.object_id) as TableName
FROM sys.dm_db_index_physical_stats (DB_ID(),null, NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc

CREATE FUNCTION dbo.IsNullOrEmpty(@text nvarchar(max)) returns bit as
BEGIN
 RETURN IIF( @text IS NULL OR LEN(@text) = 0, 1, 0)
END

        private static void SqlBulkCopy(string tableName, DataTable dataTable, SqlConnection connection)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "[" + tableName + "]";
                foreach (DataColumn dtColum in dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dtColum.ColumnName, dtColum.ColumnName);
                }
                bulkCopy.WriteToServer(dataTable);
            }
        }

select 'row.' + COLUMN_NAME + ' = ' 
+ IIF(DATA_TYPE = 'nvarchar','reader["'+COLUMN_NAME+'"] as string','')
+ IIF(DATA_TYPE = 'uniqueidentifier' and Is_nullable = 'NO','Guid.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'uniqueidentifier' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? Guid.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (Guid?)null','')
+ IIF(DATA_TYPE = 'bit' and Is_nullable = 'NO','bool.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'bit' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? bool.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (bool?)null','')
+ IIF(DATA_TYPE = 'int' and Is_nullable = 'NO','int.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'int' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? int.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (int?)null','')
+ IIF(DATA_TYPE = 'timestamp','reader["'+COLUMN_NAME+'"] as byte[]','')
-- another types add here
+';'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '@TABLE_NAME'