static void Main(string[] args) { var connection = new SqlClient.SqlConnection(args[0]); connection.Open(); Console.WriteLine("connected"); var serverConnection = new Common.ServerConnection(connection); var server = new Smo.Server(serverConnection); var db = new Smo.Database(server, "master"); Console.WriteLine(db.ToString()); var results = db.ExecuteWithResults("SELECT * FROM sys.tables"); DoQuery(db); while (true) { Console.WriteLine("Want to try again?"); var key = Console.ReadKey(true); if (key.KeyChar.Equals('n')) { break; } try { DoQuery(db); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } } }
/// <summary> /// Get missing indexes : based on missing index dmv /// </summary> /// <param name="d">your smo database</param> /// <returns>a datatable</returns> public static DataTable GetMissingIndexes(this smo.Database d) { string sql = @"SELECT mid.[statement] AS [Table] , mid.equality_columns AS [Equality Columns] , mid.inequality_columns AS [Inequality Columns] , mid.included_columns AS [Included Columns] , migs.unique_compiles AS [Unique Compiles] , migs.last_user_seek AS [Last User Seek] , migs.user_seeks AS [User Seeks] , ROUND(migs.avg_total_user_cost, 2) AS [Avg Total User Cost] , migs.avg_user_impact AS [Avg User Impact] , ROUND(user_seeks * avg_total_user_cost * (avg_user_impact * 0.01), 2) AS [Index Advantage] , 'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID,mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS [__script] FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() ORDER BY [Index Advantage] DESC"; return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get replication details (command delivered/undelivered) by publication, subscriber /// Must be executed on distribution db /// </summary> /// <param name="d">Your smo database (distribution)</param> /// <returns>a datatable</returns> public static DataTable GetReplicationDetails(this smo.Database d) { DataTable dt = new DataTable(); if (d.IsDistributor()) { string sql = @"SELECT SUM(ds.UndelivCmdsInDistDB) AS [Undelivered] , SUM(ds.DelivCmdsInDistDB) AS [Delivered] , ss.name AS [Subscriber] , sp.name AS [Publisher] , da.publication AS [Publication] , da.publisher_db AS [Database] , da.name AS [Agent] FROM dbo.MSdistribution_agents da WITH (READUNCOMMITTED) INNER JOIN sys.servers sp WITH (READUNCOMMITTED) ON sp.server_id = da.publisher_id INNER JOIN sys.servers ss WITH (READUNCOMMITTED) ON ss.server_id = da.subscriber_id INNER JOIN dbo.MSdistribution_status ds WITH (READUNCOMMITTED) ON ds.agent_id = da.id GROUP BY sp.name , ss.name , da.name , da.id , da.publication , da.publisher_db ORDER BY undelivered DESC , delivered DESC"; dt = d.ExecuteWithResults(sql).Tables[0]; } return(dt); }
/// <summary> /// Get the list of Foreign Keys not indexed. It's often a good idea to index them (not always!) /// You can easily customize this query and add/remove columns since Kankuru Datagrid autogenerate columns /// </summary> /// <param name="d">your smo database</param> /// <returns>a DataTable with the result of the query.</returns> public static DataTable GetFKWithoutIndex(this smo.Database d) { string sql = @"SELECT s.name AS [Schema Name] , OBJECT_NAME(fk.parent_object_id) AS [Table Name] , c.name AS [Column Name] , fk.name AS [Constraint Name] , fk.is_disabled AS [Is Disabled] FROM sys.foreign_keys fk (NOLOCK) INNER JOIN sys.foreign_key_columns fc (NOLOCK) ON fk.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.columns c (NOLOCK) ON fc.parent_column_id = c.column_id AND fc.parent_object_id = c.object_id INNER JOIN sys.tables t (NOLOCK) ON fk.parent_object_id = t.object_id INNER JOIN sys.schemas s (NOLOCK) ON t.schema_id = s.schema_id WHERE NOT EXISTS ( SELECT * FROM sys.tables t (NOLOCK) INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = t.object_id INNER JOIN sys.columns c2 (NOLOCK) ON t.object_id = c2.object_id INNER JOIN sys.index_columns ic (NOLOCK) ON ic.object_id = t.object_id AND i.index_id = ic.index_id AND ic.column_id = c2.column_id WHERE t.type = 'U' AND t.name = OBJECT_NAME(fk.parent_object_id) AND c2.name = c.name)" ; return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Return true is the database is a distributor database /// </summary> /// <param name="d">your smo database</param> /// <returns>a bool</returns> public static bool IsDistributor(this smo.Database d) { string sql = string.Format("SELECT 1 FROM sys.databases WHERE is_distributor = 1 AND name = '{0}'", d.Name); DataSet ds = d.ExecuteWithResults(sql); return(ds.Tables[0].Rows.Count == 1); }
/// <summary> /// Get commands count by article /// Must be executed on distribution db /// </summary> /// <param name="d">Your smo database (distribution)</param> /// <returns>a datatable</returns> public static DataTable GetReplicationCommandsByArticle(this smo.Database d, string subscriber, string database) { DataTable dt = new DataTable(); if (d.IsDistributor()) { string sql = string.Format(@"SELECT s.name AS [Subscriber] , sub.publisher_db AS [Database] , a.article AS [Article] , COUNT(*) AS [Commands Count] FROM dbo.MSrepl_commands c (NOLOCK) INNER JOIN dbo.MSsubscriptions sub (NOLOCK) ON c.publisher_database_id = sub.publisher_database_id AND c.article_id = sub.article_id INNER JOIN dbo.MSarticles a (NOLOCK) ON sub.publisher_id = a.publisher_id AND sub.publication_id = a.publication_id AND sub.article_id = a.article_id INNER JOIN sys.servers s ON sub.subscriber_id = s.server_id WHERE s.name = '{0}' AND sub.publisher_db = '{1}' GROUP BY s.name , sub.publisher_db , a.article ORDER BY [Commands Count] DESC , [Subscriber]", subscriber, database); dt = d.ExecuteWithResults(sql).Tables[0]; } return(dt); }
static void DoQuery(Smo.Database db) { Console.WriteLine("Executing..."); var results = db.ExecuteWithResults("SELECT * FROM sys.tables"); Console.WriteLine(results.Tables[0].Columns[0].ToString()); Console.WriteLine(results.Tables[0].Columns[1].ToString()); Console.WriteLine(results.Tables[0].Columns[2].ToString()); Console.WriteLine(results.Tables[0].Columns[3].ToString()); }
private void GetTop100Rows() { int rowCount = 100; string sqlQuery = string.Empty; try { this._top100Rows = new List <Dictionary <string, string> >(); SMO.Database smoDb = _context.SmoServer.Databases[this._dbName]; if (smoDb == null) { Log.Warning("Database '{0}' not found. No Rows to display.", this._dbName); return; } SMO.Table smoTable = smoDb.Tables[this._tableName, this._schemaName]; if (smoTable == null) { Log.Warning("Table '{0}' not found in Schema '{1}' in Database '{2}'. No Rows to display.", this._tableName, this._schemaName, this._dbName); return; } // fetch top 100 rows from table sqlQuery = String.Format("SELECT TOP {0} * FROM [{1}].[{2}] WITH(NOLOCK)", rowCount, smoTable.Schema, smoTable.Name); Log.Information("Database: {0}, Schema: {1}, Table: {2}. Running SMO ExecuteWithResults: {3}", smoDb.Name, smoTable.Schema, smoTable.Name, sqlQuery); using (DataSet dataset = smoDb.ExecuteWithResults(sqlQuery)) { if ((dataset != null) && (dataset.Tables.Count > 0) && (dataset.Tables[0].Rows.Count > 0)) { // Loop through all rows in the table foreach (DataRow datarow in dataset.Tables[0].Rows) { // Loop through all cells in row int columIndex = 0; Dictionary <string, string> rowToAdd = new Dictionary <string, string>(); foreach (object dataObj in datarow.ItemArray) { rowToAdd[smoTable.Columns[columIndex].Name] = dataObj.ToString(); columIndex++; } _top100Rows.Add(rowToAdd); } } } } catch (Exception e) { Log.Error("Error running query: {0}\n\n{1}", sqlQuery, e.ToString()); } }
/// <summary> /// Get list of Tables with access statistics. /// Note : these statistics are deleted when SQL Server restarts /// </summary> /// <param name="d">your smo database</param> /// <returns>a DataTable with the result of the query.</returns> public static DataTable GetLastAccessByTable(this smo.Database d) { return(d.ExecuteWithResults(@"WITH agg as ( SELECT [object_id] , last_user_seek , last_user_scan , last_user_lookup , last_user_update FROM sys.dm_db_index_usage_stats (NOLOCK) WHERE database_id = DB_ID() ) SELECT MAX(last_read) AS last_read , max (last_write) as last_write , object_id INTO #TEMP FROM ( SELECT [object_id] , last_user_seek , null FROM agg UNION all SELECT [object_id] , last_user_scan , null FROM agg UNION all SELECT [object_id] , last_user_lookup , null FROM agg UNION all SELECT [object_id] , null , last_user_update FROM agg) AS x (object_id , last_read , last_write) GROUP BY object_id SELECT s.name AS [Schema Name] , t.name AS [Table Name] , temp.last_read AS [Last Read] , temp.last_write AS [Last Write] , CASE WHEN COALESCE(temp.last_read, 0) > COALESCE(temp.last_write, 0) THEN temp.last_read ELSE temp.last_write END AS [Last Access] , t.create_date AS [Create Date] , t.modify_date AS [Last Modify Date] FROM sys.tables t (NOLOCK) INNER JOIN sys.schemas s (NOLOCK) ON t.schema_id = s.schema_id LEFT JOIN #TEMP temp (NOLOCK) ON t.object_id = temp.object_id ORDER BY [Last Access] DESC DROP TABLE #TEMP").Tables[0]); }
public static int FetchRowCount(string tableName) { try { DataSet ds = db.ExecuteWithResults(string.Format("SELECT COUNT(*) FROM dbo.{0}", tableName)); return((int)ds.Tables[0].Rows[0][0]); } catch (Exception ex) { MessageBox.Show(string.Format("SQL Error:\n\n{0}", ex.Message), "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(0); }
/// <summary> /// Get list of Heap Indexes for the specified database /// </summary> /// <param name="d">your smo database</param> /// <returns>a DataTable with the result of the query.</returns> public static DataTable GetHeapIndex(this smo.Database d) { return(d.ExecuteWithResults(@"SELECT s.name + '.' + t.name AS [Table Name] , create_date AS [Create Date] , modify_date AS [Modify Date] FROM sys.indexes i (NOLOCK) INNER JOIN sys.tables t (NOLOCK) ON t.object_id = i.object_id INNER JOIN sys.schemas s (NOLOCK) ON t.schema_id = s.schema_id WHERE i.[type] = 0 ORDER BY [Table Name]").Tables[0]); }
/// <summary> /// Get the checksum for a table /// Checksum doesn't manage these types : Xml, Image, Geography, ntext, text /// </summary> /// <param name="t">a smo.table</param> /// <returns>Int64 represents Checksum_agg(Checksum(column list))</returns> public static Int64 DataChecksum(this smo.Table t) { string column = GetJoinedColumns(t); string query = string.Format("select checksum_agg(CHECKSUM({0})) as nb from [{1}].[{2}]", column, t.Schema, t.Name); smo.Database d = t.Parent; DataSet ds = d.ExecuteWithResults(query); Int64 i; Int64.TryParse(ds.Tables[0].Rows[0]["nb"].ToString(), out i); return(i); }
public static DataTable DashboardSsrs(this smo.Database d, TimeSpan logFrom) { string sql = string.Format(@"SELECT EL.TimeStart AS LogDate , EL.Status + '"" on ""' + CASE(ReportAction) WHEN 1 THEN 'Render' WHEN 2 THEN 'BookmarkNavigation' WHEN 3 THEN 'DocumentMapNavigation' WHEN 4 THEN 'DrillThrough' WHEN 5 THEN 'FindString' WHEN 6 THEN 'GetDocumentMap' WHEN 7 THEN 'Toggle' WHEN 8 THEN 'Sort' WHEN 9 THEN 'Execute' WHEN 10 THEN 'RenderEdit' ELSE 'Unknown' END + '"" the report ""' + ISNULL(C.Name, EL.ReportID) + '"". The username is ""' + EL.UserName + '""' AS texte FROM ExecutionLogStorage EL (NOLOCK) LEFT JOIN Catalog C (NOLOCK) ON EL.ReportID = C.ItemID WHERE EL.Status != 'rsSuccess' AND EL.TimeStart >= DATEADD(DAY, -{0}, DATEADD(HOUR, -{1}, DATEADD(MINUTE, -{2}, GETDATE()))) UNION ALL SELECT LastRunTime AS LogDate , 'Subscription ' + coalesce(c.Name, '') + ' : ' + LastStatus AS texte FROM Subscriptions s LEFT JOIN Catalog c ON s.Report_OID = c.ItemID WHERE s.LastStatus LIKE 'Failure%' AND LastRunTime >= DATEADD(DAY, -{0}, DATEADD(HOUR, -{1}, DATEADD(MINUTE, -{2}, GETDATE()))) ORDER BY LogDate DESC", logFrom.Days, logFrom.Hours, logFrom.Minutes); if (d.Parent.VersionMajor < 10) { sql = string.Format(@"SELECT EL.TimeStart AS LogDate , EL.Status + '"" on the report ""' + ISNULL(C.Name, EL.ReportID) + '"". The username is ""' + EL.UserName + '""' AS texte FROM ExecutionLog EL (NOLOCK) LEFT JOIN Catalog C (NOLOCK) ON EL.ReportID = C.ItemID WHERE EL.Status != 'rsSuccess' AND EL.TimeStart >= DATEADD(DAY, -{0}, DATEADD(HOUR, -{1}, DATEADD(MINUTE, -{2}, GETDATE()))) UNION ALL SELECT LastRunTime AS LogDate , 'Subscription ' + coalesce(c.Name, '') + ' : ' + LastStatus AS texte FROM Subscriptions s LEFT JOIN Catalog c ON s.Report_OID = c.ItemID WHERE s.LastStatus like 'Failure%' AND LastRunTime >= DATEADD(DAY, -{0}, DATEADD(HOUR, -{1}, DATEADD(MINUTE, -{2}, GETDATE()))) ORDER BY LogDate DESC", logFrom.Days, logFrom.Hours, logFrom.Minutes); } return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get transactions opened for each replicated databases /// Must be executed on distribution db /// </summary> /// <param name="d">Your smo database (distribution)</param> /// <returns>a datatable</returns> public static DataTable GetReplicationTransactionsByDatabase(this smo.Database d) { DataTable dt = new DataTable(); if (d.IsDistributor()) { string sql = @"SELECT d.publisher_db AS [Database] , COUNT(*) AS [Transactions Count] FROM MSrepl_transactions t INNER JOIN MSpublisher_databases d ON t.publisher_database_id = d.id GROUP BY d.publisher_db ORDER BY [Transactions Count] DESC"; dt = d.ExecuteWithResults(sql).Tables[0]; } return(dt); }
public static DataTable GetLastExecutionByProcedure(this smo.Database d) { return(d.ExecuteWithResults(@"SELECT sc.name AS [Schema] , p.name AS [Procedure Name] , SUM(execution_count) AS [Execution Count] , MAX (last_execution_time) AS [Last Execution Time] FROM sys.procedures p INNER JOIN sys.schemas sc ON p.schema_id = sc.schema_id LEFT JOIN sys.dm_exec_procedure_stats s ON sc.name = OBJECT_SCHEMA_NAME(s.object_id, database_id) AND p.name = OBJECT_NAME(s.object_id, database_id) AND s.database_id = DB_ID() GROUP BY sc.name , p.name ORDER BY sc.name , p.name" ).Tables[0]); }
public int FetchRowCount(string tableName) { try { DataSet ds = db.ExecuteWithResults(string.Format("SELECT COUNT(*) FROM dbo.{0}", tableName)); return((int)ds.Tables[0].Rows[0][0]); } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); lManager.Enter(Logs.Sender.DATA, Logs.Level.SQL_ERROR, ex); } return(0); }
/// <summary> /// Get list of Tables without Primary Key /// </summary> /// <param name="d">your smo database</param> /// <returns>a DataTable with the result of the query.</returns> public static DataTable GetTableWithoutPK(this smo.Database d) { return(d.ExecuteWithResults(@"SELECT s.name as [Schema Name] , t.name as [Table Name] FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.is_ms_shipped = 0 AND NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c WHERE c.CONSTRAINT_TYPE = 'PRIMARY KEY' AND s.name = c.TABLE_SCHEMA AND t.name = c.TABLE_NAME ) ORDER BY s.name , t.name" ).Tables[0]); }
/// <summary> /// Get Buffer detail for a database. What is the memory usage by object ? /// </summary> /// <param name="d">your smo database</param> /// <returns>DataTable with the result of the query</returns> public static DataTable GetBufferStats(this smo.Database d) { string compression1 = " , p.data_compression_desc AS CompressionType "; string compression2 = " , p.data_compression_desc "; string compression3 = " , cte.CompressionType AS [Compression Type]"; if (d.Parent.VersionMajor < 10) { compression1 = string.Empty; compression2 = string.Empty; compression3 = string.Empty; } string sql = string.Format(@"WITH cte AS ( SELECT p.object_id , p.index_id , COUNT(*) / 128 AS Buffer_size , COUNT(*) AS BufferCount , a.type_desc , p.rows {0} FROM sys.dm_os_buffer_descriptors b (NOLOCK) LEFT JOIN sys.allocation_units a (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id LEFT JOIN sys.partitions p (NOLOCK) ON a.container_id = p.partition_id WHERE b.database_id = CONVERT(int, DB_ID()) GROUP BY p.object_id , p.index_id {1} , a.type_desc , p.rows ) SELECT COALESCE(COALESCE(OBJECT_SCHEMA_NAME(cte.object_id), '') + '.' + OBJECT_NAME(cte.object_id), 'Unused') AS [Table] , i.name AS [Index] , cte.Buffer_size AS [Buffer Size] , cte.BufferCount AS [Buffer Count] {2} , cte.type_desc AS [Allocation Unit Type] , cte.rows AS [Rows] FROM cte LEFT JOIN sys.indexes i(NOLOCK) ON cte.index_id = i.index_id AND cte.object_id = i.object_id ORDER BY cte.BufferCount DESC", compression1, compression2, compression3); return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get all column occurences in a database for a given column name. /// </summary> /// <param name="d">your smo database</param> /// <param name="column">Datatable</param> /// <returns></returns> public static DataTable GetAllColumnsWithName(this smo.Database d, string column) { string _sql = string.Format(@"SELECT s.name + '.' + t.name AS tablename , ty.name AS typeName , c.max_length , c.precision FROM sys.columns c (NOLOCK) INNER JOIN sys.tables t (NOLOCK) ON c.object_id = t.object_id INNER JOIN sys.schemas s (NOLOCK) ON t.schema_id = s.schema_id INNER JOIN sys.types ty (NOLOCK) ON c.user_type_id = ty.user_type_id WHERE c.name = '{0}' ORDER BY typeName , c.max_length , c.precision , t.name" , column); return(d.ExecuteWithResults(_sql).Tables[0]); }
/// <summary> /// Get list of Tables without Clustered Index /// </summary> /// <param name="d">your smo database</param> /// <returns>a DataTable with the result of the query.</returns> public static DataTable GetTableWithoutClusteredIndex(this smo.Database d) { return(d.ExecuteWithResults(@"SELECT s.name AS [Schema Name] , t.name AS [Table Name] FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.is_ms_shipped = 0 AND NOT EXISTS ( SELECT * FROM sys.tables st INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id INNER JOIN sys.indexes i ON i.object_id = st.object_id WHERE i.type = 1 AND s.name = ss.name AND t.name = st.name ) ORDER BY t.name").Tables[0]); }
/// <summary> /// get error from distribution /// </summary> /// <param name="d">your smo distribution database</param> /// <param name="startDate"></param> /// <returns>a datatable</returns> public static DataTable GetReplicationErrorLog(this smo.Database d, DateTime startDate) { DataTable dt = new DataTable(); if (d.IsDistributor()) { string sql = string.Format(@"IF EXISTS(SELECT * FROM sys.objects WHERE name = 'MSrepl_errors') BEGIN SELECT TOP 10000 DB_NAME() [Database] , [time] , source_name AS [Source Name] , error_text AS [Error Text] FROM dbo.MSrepl_errors WITH (READUNCOMMITTED) WHERE [time] >= '{0}' order by time desc END", startDate.ToString("yyyyMMdd hh:mm:ss")); dt = d.ExecuteWithResults(sql).Tables[0]; } return(dt); }
/// <summary> /// Get expensive indexes : when index writes are greater than reads /// </summary> /// <param name="d">your smo database</param> /// <returns>a datatable</returns> public static DataTable GetExpensiveIndexes(this smo.Database d) { string sql = @"SELECT sc.name + '.' + o.name AS [Table] , i.name AS [Index] , s.user_updates AS [User Update] , s.user_seeks AS [User Seeks] , s.user_scans AS [User Scans] , s.user_lookups AS [User Lookups] , s.user_seeks + s.user_scans + s.user_lookups AS [Total Read] FROM sys.indexes i (nolock) INNER JOIN sys.dm_db_index_usage_stats s (nolock) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id WHERE OBJECTPROPERTY(i.[object_id], 'ismsshipped') = 0 AND i.name IS NOT NULL AND s.user_updates > (user_seeks + user_scans + user_lookups) ORDER BY user_updates DESC"; return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get average latency/rate history from distribution database, group by 10 minutes /// </summary> /// <param name="d">Your smo database (distribution)</param> /// <param name="startDate">Start DateTime</param> /// <param name="endDate">End DateTime</param> /// <returns>a datatable</returns> public static DataTable GetReplicationLatencyStats(this smo.Database d, DateTime startDate, DateTime endDate) { DataTable dt = new DataTable(); if (d.IsDistributor()) { string sql = string.Format(@"IF EXISTS(SELECT * FROM sys.objects WHERE name = 'MSdistribution_history') BEGIN SELECT CONVERT(CHAR(15), time, 121) + '0:00' dt , ROUND(AVG(CAST(current_delivery_latency AS bigint)), 0) AS [Average Delivery Latency] , ROUND(AVG(CAST(current_delivery_rate AS bigint)), 0) [Average Delivery Rate] , db_name() as [Database] FROM dbo.MSdistribution_history WITH (READUNCOMMITTED) WHERE current_delivery_latency > 0 AND time BETWEEN '{0}' AND '{1}' GROUP BY CONVERT(CHAR(15), time, 121) + '0:00' ORDER BY dt DESC END", startDate.ToString("yyyyMMdd hh:mm:ss"), endDate.ToString("yyyyMMdd hh:mm:ss")); dt = d.ExecuteWithResults(sql).Tables[0]; } return(dt); }
/// <summary> /// Compare 2 tables data. Less performant than CompareDataLight but in case of difference /// you're able to know which rows are differents /// </summary> /// <param name="t1">smo.table 1</param> /// <param name="t2">smo.table 2</param> /// <param name="dtRowsAdded">this DataTable contains rows from table1 not existing in table2</param> /// <param name="dtRowsDeleted">this DataTable contains rows from table2 not existing in table1</param> /// <param name="dtRowsUpdated">this DataTable contains rows existing in the 2 tables but different</param> /// <param name="details">if false, DataTables will contains only PK columns + checksum. If true, Datatables will contains every columns</param> /// <returns></returns> public static bool CompareData(this smo.Table t1, smo.Table t2, out DataTable dtRowsAdded, out DataTable dtRowsDeleted, out DataTable dtRowsUpdated, bool details = false) { bool hasSameData = true; dtRowsAdded = null; dtRowsDeleted = null; dtRowsUpdated = null; string columnPK1 = GetJoinedColumnsPKOnly(t1); string columnPK2 = GetJoinedColumnsPKOnly(t2); string column1 = GetJoinedColumnsWithoutPK(t1); string column2 = GetJoinedColumnsWithoutPK(t2); string queryTemplate = "SELECT {0} as pk, CHECKSUM({1}) as rowchecksum FROM [{2}].[{3}]"; if (details) { queryTemplate = "SELECT {0} as pk, CHECKSUM({1}) as rowchecksum, * FROM [{2}].[{3}]"; } string query1 = string.Format(queryTemplate, columnPK1, column1, t1.Schema, t1.Name); string query2 = string.Format(queryTemplate, columnPK2, column2, t2.Schema, t2.Name); smo.Database d1 = t1.Parent; smo.Database d2 = t2.Parent; EnumerableRowCollection <DataRow> r1 = d1.ExecuteWithResults(query1).Tables[0].AsEnumerable(); EnumerableRowCollection <DataRow> r2 = d2.ExecuteWithResults(query2).Tables[0].AsEnumerable(); bool hasRowsAdded = CompareRowsAdded(r1, r2, out dtRowsAdded); bool hasRowsDeleted = CompareRowsAdded(r2, r1, out dtRowsDeleted); bool hasRowsUpdated = CompareRowsUpdated(r2, r1, out dtRowsUpdated); if (hasRowsAdded || hasRowsDeleted || hasRowsUpdated) { hasSameData = false; } return(hasSameData); }
/// <summary> /// Find all columns in a database with the same name but with different datatype /// </summary> /// <param name="d">your smo Database</param> /// <returns>a datatable</returns> public static DataTable GetColumnWithSameNameButDifferentType(this smo.Database d) { string _sql = @"SELECT DISTINCT columnName AS [Column Name] FROM ( SELECT ss.columnName , ss.typeName , ss.max_length , ss.precision , SUM(ss.nb) AS sumnb , ROW_NUMBER() OVER (PARTITION BY ss.columnName ORDER BY ss.columnName) AS rid FROM ( SELECT c.name AS columnName , ty.name AS typeName , c.max_length , c.precision , COUNT(*) AS nb FROM sys.columns c (NOLOCK) INNER JOIN sys.tables t (NOLOCK) on c.object_id = t.object_id INNER JOIN sys.types ty (NOLOCK) on c.user_type_id = ty.user_type_id GROUP BY c.name , ty.name , c.max_length , c.precision )ss GROUP BY ss.columnName , ss.typeName , ss.max_length , ss.precision )sss WHERE rid > 1 ORDER BY sss.columnName"; return(d.ExecuteWithResults(_sql).Tables[0]); }
/// <summary> /// Get the backup history for a given database /// </summary> /// <param name="d">your smo database</param> /// <param name="limit">This param will be used in the SELECT TOP XXX</param> /// <returns>a DataTable with the result of the query</returns> public static DataTable GetBackupHistory(this smo.Database d, int limit = 1000) { string sql = string.Format(@"SELECT TOP {0} CASE s.[type] WHEN 'D' THEN CASE WHEN s.is_snapshot = 1 THEN 'Snapshot' ELSE 'Full' END WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' ELSE 'Other' END AS [Backup Type] , m.physical_device_name AS [Physical Device Name] , CAST(CAST(s.compressed_backup_size / 1048576 AS INT) AS VARCHAR(20)) + ' ' + 'MB' AS [Backup Size (MB)] , CAST(CAST(100 - (compressed_backup_size / backup_size * 100) as DECIMAL(9,2)) as VARCHAR(10)) + ' %' AS [Compression Ratio] , CAST(DATEDIFF(SECOND, s.backup_start_date, s.backup_finish_date) AS VARCHAR(5)) + ' ' + 'Seconds' AS [Time Taken] , s.backup_start_date AS [Backup Start Date] , s.backup_finish_date AS [Backup Finish Date] , CAST(s.first_lsn AS VARCHAR(25)) AS [First LSN] , CAST(s.last_lsn AS VARCHAR(25)) AS [Last LSN] , CAST(s.compressed_backup_size / 1048576 as INT) as __bkSizeInt , DATEDIFF(second, s.backup_start_date, s.backup_finish_date) __TimeTakenInt , CASE s.[type] WHEN 'D' THEN CASE WHEN s.is_snapshot = 1 THEN '#327AC1FF' ELSE '#32FF0000' END WHEN 'I' THEN '#320AFF0E' WHEN 'L' THEN '#32FFFF00' ELSE '#00000000' END AS __rowColor FROM msdb.dbo.backupset s (NOLOCK) INNER JOIN msdb.dbo.backupmediafamily m (NOLOCK) ON s.media_set_id = m.media_set_id WHERE s.database_name = '{1}' ORDER BY backup_start_date DESC , backup_finish_date", limit, d.Name); if (d.Parent.VersionMajor < 10) { sql = string.Format(@"SELECT TOP {0} CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' ELSE 'Other' END AS [Backup Type] , m.physical_device_name AS [Physical Device Name] , CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(20)) + ' ' + 'MB' AS [Backup Size (MB)] , '-' AS [Compression Ratio] , CAST(DATEDIFF(SECOND, s.backup_start_date, s.backup_finish_date) AS VARCHAR(5)) + ' ' + 'Seconds' AS [Time Taken], , s.backup_start_date AS [Backup Start Date] , s.backup_finish_date AS [Backup Finish Date] , s.first_lsn AS [First LSN] , s.last_lsn AS [Last LSN] , CAST(s.backup_size / 1048576 as INT) as __bkSizeInt , DATEDIFF(second, s.backup_start_date, s.backup_finish_date) __TimeTakenInt , CASE s.[type] WHEN 'D' THEN '#32FF0000' WHEN 'I' THEN '#320AFF0E' WHEN 'L' THEN '#32FFFF00' ELSE '#00000000' END AS __rowColor FROM msdb.dbo.backupset s (NOLOCK) INNER JOIN msdb.dbo.backupmediafamily m (NOLOCK) ON s.media_set_id = m.media_set_id WHERE s.database_name = {1} ORDER BY backup_start_date DESC , backup_finish_date" , limit, d.Name); } return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get the statistics options for the database /// </summary> public static DataTable StatisticsConfiguration(this smo.Database d) { string sql = @"DECLARE @SQL NVARCHAR(4000) SET @SQL = 'SELECT {0} AS is_auto_update_stats_on , {1} AS is_auto_update_stats_async_on , {2} AS is_auto_create_stats_on , {3} AS is_auto_create_stats_incremental_on FROM sys.databases WHERE database_id = DB_ID()' IF EXISTS(SELECT * FROM sys.all_columns c WHERE c.name = 'is_auto_update_stats_on' AND OBJECT_NAME(c.object_id) = 'databases') BEGIN SET @SQL = REPLACE(@SQL, '{0}', 'is_auto_update_stats_on') END ELSE BEGIN SET @SQL = REPLACE(@SQL, '{0}', '''N/A''') END IF EXISTS(SELECT * FROM sys.all_columns c WHERE c.name = 'is_auto_update_stats_async_on' AND OBJECT_NAME(c.object_id) = 'databases') BEGIN SET @SQL = REPLACE(@SQL, '{1}', 'is_auto_update_stats_async_on') END ELSE BEGIN SET @SQL = REPLACE(@SQL, '{1}', '''N/A''') END IF EXISTS(SELECT * FROM sys.all_columns c WHERE c.name = 'is_auto_create_stats_on' AND OBJECT_NAME(c.object_id) = 'databases') BEGIN SET @SQL = REPLACE(@SQL, '{2}', 'is_auto_create_stats_on') END ELSE BEGIN SET @SQL = REPLACE(@SQL, '{2}', '''N/A''') END IF EXISTS(SELECT * FROM sys.all_columns c WHERE c.name = 'is_auto_create_stats_incremental_on' AND OBJECT_NAME(c.object_id) = 'databases') BEGIN SET @SQL = REPLACE(@SQL, '{3}', 'is_auto_create_stats_incremental_on') END ELSE BEGIN SET @SQL = REPLACE(@SQL, '{3}', '''N/A''') END EXECUTE sys.sp_executesql @SQL"; return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get duplicated indexes. Duplicated indexes are 2 indexes using the same column. /// Inspired by http://blog.developpez.com/sqlpro/p9263/langage-sql-norme/une_requete_recherchant_les_index_anorma /// You can easily customize this query and add/remove columns since Kankuru Datagrid autogenerate columns /// </summary> /// <param name="d">your smo database</param> /// <returns>a DataTable with the result of the query.</returns> public static DataTable GetDuplicatedIndex(this smo.Database d) { string sqlFilter = @", i1.filter_definition AS [Filter Definition] , i2.filter_definition AS [Similar Filter Definition] "; if (d.Parent.Version.Major < 10) { sqlFilter = string.Empty; } string sql = string.Format(@"WITH T0 AS ( SELECT ic.object_id , index_id , c.column_id , key_ordinal , CASE is_descending_key WHEN '0' THEN 'ASC' WHEN '1' THEN 'DESC' END AS sens , c.name AS column_name , ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id ORDER BY key_ordinal DESC) AS N , is_included_column FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE key_ordinal > 0 AND index_id > 0) , T1 AS ( SELECT object_id , index_id , column_id , key_ordinal , N , CASE WHEN is_included_column = 0 THEN CAST(column_name AS VARCHAR(MAX)) + ' ' + sens ELSE '' END AS LitteralComp , CASE WHEN is_included_column = 0 THEN CAST(column_id AS VARCHAR(MAX)) + SUBSTRING(sens, 1, 1) ELSE '' END AS MathComp , MAX(N) OVER(PARTITION BY object_id, index_id) AS CMax , CASE WHEN is_included_column = 1 THEN CAST(column_name AS VARCHAR(MAX)) ELSE '' END AS IncludedColumns FROM T0 WHERE key_ordinal = 1 UNION ALL SELECT T0.object_id , T0.index_id , T0.column_id , T0.key_ordinal , T0.N , LitteralComp + CASE WHEN is_included_column = 0 THEN ', ' + CAST(T0.column_name AS VARCHAR(MAX)) + ' ' + T0.sens ELSE '' END , MathComp + CASE WHEN is_included_column = 0 THEN CAST(T0.column_id AS VARCHAR(MAX)) + SUBSTRING(T0.sens, 1, 1) ELSE '' END , T1.CMax, IncludedColumns + CASE WHEN is_included_column = 1 THEN ', ' + CAST(column_name AS VARCHAR(MAX)) ELSE '' END FROM T0 INNER JOIN T1 ON T0.object_id = T1.object_id AND T0.index_id = T1.index_id AND T0.key_ordinal = T1.key_ordinal + 1) , T2 AS ( SELECT object_id , index_id , LitteralComp , MathComp , CMax , IncludedColumns FROM T1 WHERE N = 1) , T4 AS ( SELECT T2.object_id , T2.index_id , T3.index_id AS index_id_anomalie , T2.LitteralComp AS IndexKey , T3.LitteralComp AS SimilarIndexKey , T2.IncludedColumns , T3.IncludedColumns AS SimilarIncludedColumns , CASE WHEN T2.MathComp = T3.MathComp THEN 'Duplicated' WHEN T2.MathComp LIKE T3.MathComp +'%' THEN 'Included' END AS DuplicationType , ABS(T2.CMax - T3.CMax) AS Distance FROM T2 INNER JOIN T2 AS T3 ON T2.object_id = T3.object_id AND T2.index_id <> T3.index_id AND T2.MathComp LIKE T3.MathComp +'%') SELECT s.name +'.' + o.name AS [Table Name] , i1.name AS [Index Name] , i2.name AS [Similar Index] , T4.DuplicationType AS [Duplication Type] , T4.Distance , T4.IndexKey AS [Index Key] , T4.SimilarIndexKey AS [Similar Index Key] , T4.IncludedColumns AS [Included Columns] , T4.SimilarIncludedColumns AS [Similar IncludedColumns] {0} , i1.type_desc AS [Index Type] , i2.type_desc AS [Similar Index Type] , i1.is_primary_key AS [Is Primary Key] , i2.is_primary_key AS [Is Primary Key Similar] , i1.is_unique AS [Is Unique Index] , i2.is_unique AS [Is Unique Similar Index] , i1.is_unique_constraint AS [Is Unique Constraint] , i2.is_unique_constraint AS [Is Unique Constraint Similar] FROM T4 INNER JOIN sys.objects AS o ON T4.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.indexes AS i1 ON T4.object_id = i1.object_id AND T4.index_id = i1.index_id INNER JOIN sys.indexes AS i2 ON T4.object_id = i2.object_id AND T4.index_id_anomalie = i2.index_id WHERE o.type IN ('U', 'V') ORDER BY [Table Name] , [Index Name]", sqlFilter); return(d.ExecuteWithResults(sql).Tables[0]); }
public static DataTable GetTableTreeMaps(this smo.Database d, int minSize, int maxSize, bool withTables, bool withViews) { string objectTypeFilter = string.Empty; if (withTables == false || withViews == false) { if (!withTables) { objectTypeFilter = " and type != 'U' "; } if (!withViews) { objectTypeFilter = " and type != 'V' "; } } string sql = string.Format(@"WITH cte AS( SELECT a3.name AS [schemaname], a2.name AS [tablename], a2.type, a2.type_desc, a1.rows AS row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8.0 / 1024 AS reserved, a1.data * 8.0 / 1024 AS data, (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size, (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused FROM (SELECT ps.object_id , SUM( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows] , SUM(ps.reserved_page_count) AS reserved , SUM( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data , SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id , SUM(ps.reserved_page_count) AS reserved , SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ) select schemaname , tablename , row_count , type , type_desc , reserved , data , index_size , unused , CASE WHEN reserved != 0 THEN index_size * 100.0 / reserved ELSE 0 END AS IndexPercent FROM cte WHERE reserved >= {0} AND reserved < {1} {2} ORDER BY reserved DESC", minSize, maxSize, objectTypeFilter); return(d.ExecuteWithResults(sql).Tables[0]); }
/// <summary> /// Get Disabled index list in the database /// </summary> public static DataTable GetDisabledIndex(this smo.Database d) { string sqlFilter = @", i.filter_definition "; if (d.Parent.Version.Major < 10) { sqlFilter = string.Empty; } string sql = string.Format(@"WITH cteCol(index_id, object_id, schema_name, table_name, index_name, index_column_id, column_name) AS( SELECT i.index_id , i.object_id , s.name AS schema_name , t.name AS table_name , i.name AS index_name , index_column_id , CAST(c.name AS VARCHAR(MAX)) AS column_name FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id INNER JOIN sys.indexes i on i.object_id = t.object_id INNER JOIN sys.index_columns ic on ic.object_id = t.object_id AND ic.index_id=i.index_id INNER JOIN sys.columns c on c.object_id = t.object_id AND ic.column_id = c.column_id WHERE ic.index_column_id = 1 AND i.is_disabled = 1 UNION ALL SELECT i.index_id , i.object_id , s.name , t.name , i.name , ic.index_column_id , CAST(cteCol.column_name + ',' + c.name AS VARCHAR(MAX)) FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id INNER JOIN sys.indexes i on i.object_id = t.object_id INNER JOIN sys.index_columns ic on ic.object_id = t.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c on c.object_id = t.object_id AND ic.column_id = c.column_id INNER JOIN cteCol on cteCol.index_column_id + 1 = ic.index_column_id AND cteCol.schema_name = s.name AND cteCol.table_name = t.name AND cteCol.index_name = i.name WHERE i.is_disabled = 1) SELECT cteCol.schema_name + '.' + cteCol.table_name AS [Table Name] , cteCol.index_name AS [Index Name] , cteCol.column_name AS [Column Name] , i.type_desc , i.is_unique {0} FROM cteCol INNER JOIN ( SELECT schema_name , table_name , index_name , MAX(index_column_id) index_column_id FROM cteCol GROUP BY schema_name , table_name , index_name) mx ON cteCol.schema_name = mx.schema_name AND cteCol.table_name = mx.table_name AND cteCol.index_name = mx.index_name AND cteCol.index_column_id = mx.index_column_id INNER JOIN sys.indexes i ON cteCol.index_id = i.index_id AND cteCol.object_id = i.object_id ORDER BY [Table Name] , [Index Name] , [Column Name]" , sqlFilter); return(d.ExecuteWithResults(sql).Tables[0]); }