Reindex All Tables in a Database

Code for reindexing all tables in a database using dbreindex

Continue reading ...

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: [cc lang=”sql”] 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) ,DefaultConst sysname ) INSERT INTO @TableConstraints ( DefaultConst ) SELECT object_name(default_object_id) FROM sys.columns WHERE object_id = object_id(@TableSchema + ‘.’ + @TableName) AND object_name(default_object_id) IS NOT NULL SET @CountConst = (SELECT MAX(ID) FROM @TableConstraints) WHILE @CountConst > 0 BEGIN SET @Default = (SELECT DefaultConst FROM @TableConstraints WHERE ID = @CountConst) SET @SQLDropMe = ‘ALTER TABLE ‘ + @TableSchema + ‘.’ + @TableName + ‘ DROP CONSTRAINT ‘ + @default SELECT @SQLDropMe –EXEC (@SQLDropMe) SET @CountConst = @CountConst – 1 END […]

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 [cc lang=”sql”] SELECT 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 [/cc]

Continue reading ...

Find Memory Usage of Executing Procedures

To get the query memory usage of currently executing queries run the following: [cc lang=”sql”] 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 [/cc]

Continue reading ...

Index Usage DMV

The following DMV query retrieves the usage statistics for existing indexes. User Seeks – A high number indicates a well utilized index. User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns User Lookups – Indicates a different index was used for criteria and the actual data was looked up from this index for the select list User Updates – Number of times the index was updated with additional records [cc lang=”sql”] SELECT ObjectName = object_schema_name(idx.object_id) + ‘.’ + object_name(idx.object_id) ,IndexName = ,IndexType = CASE WHEN is_unique = 1 THEN ‘UNIQUE ‘ ELSE ” END + idx.type_desc ,User_Seeks = us.user_seeks ,User_Scans = us.user_scans ,User_Lookups = us.user_lookups ,User_Updates = us.user_updates FROM sys.indexes idx LEFT JOIN sys.dm_db_index_usage_stats us ON idx.object_id = us.object_id AND idx.index_id = us.index_id AND us.database_id = db_id() WHERE object_schema_name(idx.object_id) != ‘sys’ ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC [/cc]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!