Find Missing Table and Index Statistics

SELECT Last_Updated = STATS_DATE(si.id, si.indid) ,TableName = object_name(si.id) ,Name = RTRIM(si.name) ,Size = DATALENGTH (si.statblob) FROM sysindexes si WITH (nolock) WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1 --AND INDEXPROPERTY (si.id , si.name , 'IsAutoStatistics' ) = 0 order by last_updated, tablename Continue reading ...

Reindex All Tables in a Database

Code for reindexing all tables in a database using dbreindex

Dynamically Drop Table Constraints

System generated constraints take on a naming convention of their own. Unfortunately the naming convention in production is rarely the same name in the uncontrolled environments. Using this script, you can dynamically drop all system generated constraints. It doesn’t go as far are re-creating them, however it’s a start. Just change the values of the @TableSchema and TableName variables below: DECLARE @TableName      VARCHAR(100) DECLARE @TableSchema    VARCHAR(100) DECLARE @CountConst     INT DECLARE @DEFAULT        sysname DECLARE @SQLDropMe      VARCHAR(MAX) DECLARE @ColumnNames    VARCHAR(MAX) SET @TableSchema = 'dbo' SET @TableName = 'employees' --------------------------------------------- Store Existing Column Names SET @ColumnNames = SUBSTRING((SELECT             ',' + r.COLUMN_NAME             FROM(                 SELECT COLUMN_NAME                 FROM INFORMATION_SCHEMA.COLUMNS                 WHERE TABLE_SCHEMA = @TableSchema AND                     TABLE_NAME = @TableName             ) r             FOR XML PATH('')   ), 2, 8000) --------------------------------------------- /Store Existing Column Names --------------------------------------------- Insert contents into temp table EXEC ('SELECT * INTO ' + @TableSchema + '.tmp01_' + @TableName + ' FROM ' + @TableSchema + '.' + @TableName) --------------------------------------------- /Insert contents into temp table --------------------------------------------- Drop all the constraints DECLARE @TableConstraints TABLE (     ID              INT     IDENTITY(1,1)   […] Continue reading ...

The database principal owns a schema in the database, and cannot be dropped. – Fix

If you try to drop a user that owns a schema, you will receive the following error message: [code] The database principal owns a schema in the database, and cannot be dropped. [/code] In order to drop the user, you need to find the schemas they are assigned, then transfer the ownership to another user or role SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('joe') -- now use the names you find from the above query below in place of the SchemaName below ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

Find Memory Usage of Executing Procedures

To get the query memory usage of currently executing queries run the following: SELECT     TEXT     ,query_plan     ,requested_memory_kb     ,granted_memory_kb     ,used_memory_kb FROM sys.dm_exec_query_memory_grants emg CROSS APPLY sys.dm_exec_sql_text(sql_handle)   CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle) ORDER BY emg.requested_memory_kb DESC Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!