Reorganize all indexes in a Database in SQL Server.

Index Maintenance is one of the major task for a DBA. If rate of Index fragmentation increased then index de-fragmentation is become required. So we can use index reorganize option to rebuild all the indexes. Use Index reorganize option when rate of index fragmentation is b/w 10% to  40%.

Syntax:

Alter Index All On Table_Name Reorganize.

Example:

ALTER INDEX ALL ON geographicals Reorganize

GO

 

Rebuild Index for All tablex in database:

Exec sp_msforeachtable 'ALTER INDEX ALL ON ? Reorganize'

GO

Reference: 

SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

SET NOCOUNT ON

DECLARE @table TABLE (
 RowId INT PRIMARY KEY IDENTITY(1, 1)
 ,ForeignKeyConstraintName NVARCHAR(200)
 ,ForeignKeyConstraintTableSchema NVARCHAR(200)
 ,ForeignKeyConstraintTableName NVARCHAR(200)
 ,ForeignKeyConstraintColumnName NVARCHAR(200)
 ,PrimaryKeyConstraintName NVARCHAR(200)
 ,PrimaryKeyConstraintTableSchema NVARCHAR(200)
 ,PrimaryKeyConstraintTableName NVARCHAR(200)
 ,PrimaryKeyConstraintColumnName NVARCHAR(200)
 )

INSERT INTO @table (
 ForeignKeyConstraintName
 ,ForeignKeyConstraintTableSchema
 ,ForeignKeyConstraintTableName
 ,ForeignKeyConstraintColumnName
 )
SELECT U.CONSTRAINT_NAME
 ,U.TABLE_SCHEMA
 ,U.TABLE_NAME
 ,U.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table
SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA
 ,PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table
SET PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT '
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
 
GO'
FROM @table

--ADD CONSTRAINT:
SELECT '
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
 
GO'
FROM @table
GO






Reference:https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

List all Orphan users in a SQL Server database

CREATE TABLE ##ORPHANUSER (
DBNAME VARCHAR(100)
,USERNAME VARCHAR(100)
,CREATEDATE VARCHAR(100)
,USERTYPE VARCHAR(100)
)
EXEC SP_MSFOREACHDB ' USE [?]
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE 
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN''
WHEN ISNTGROUP = 1 THEN ''NT GROUP''
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN''
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins)'
SELECT *
FROM ##ORPHANUSER
DROP TABLE ##ORPHANUSER

Find text in SQL Server Agent Jobs

USE MSDBGO
SELECT Job.name AS JobName
,Job.enabled AS ActiveStatus
,JobStep.step_name AS JobStepName
,JobStep.command AS JobCommandFROM sysjobs JobINNER JOIN sysjobsteps JobStep ON Job.job_id = JobStep.job_idWHERE JobStep.command LIKE '%YourTextHere%'

List tables on their dependency order based on foreign keys for Data Migration

If you want to perform bulk insert operation on multiple tables.

The tables have foreign key relationships between themselves.

If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.



Requirement



Produce a list of tables within a database ordered according to their dependencies.

Tables with no dependencies (no foreign keys) will be 1st.

Tables with dependencies only in the 1st set of tables will be 2nd.

Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.

and so on...





WITH cte (
 lvl
 ,object_id
 ,name
 ,schema_Name
 )
AS (
 SELECT 1
  ,object_id
  ,sys.tables.name
  ,sys.schemas.name AS schema_Name
 FROM sys.tables
 INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE type_desc = 'USER_TABLE'
  AND is_ms_shipped = 0
 
 UNION ALL
 
 SELECT cte.lvl + 1
  ,t.object_id
  ,t.name
  ,S.name AS schema_Name
 FROM cte
 JOIN sys.tables AS t ON EXISTS (
   SELECT NULL
   FROM sys.foreign_keys AS fk
   WHERE fk.parent_object_id = t.object_id
    AND fk.referenced_object_id = cte.object_id
   )
 JOIN sys.schemas AS S ON t.schema_id = S.schema_id
  AND t.object_id <> cte.object_id
  AND cte.lvl < 30
 WHERE t.type_desc = 'USER_TABLE'
  AND t.is_ms_shipped = 0
 )
SELECT schema_Name
 ,name
 ,MAX(lvl) AS dependency_level
FROM cte
WHERE schema_Name LIKE '%ORCA%'
GROUP BY schema_Name
 ,name
ORDER BY dependency_level
 ,schema_Name
 ,name;



Reference

Calculate disk space occupied by each schema in SQL Server

SELECT SCHEMA_NAME(so.schema_id) AS SchemaName
,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMBFROM sys.dm_db_partition_stats psJOIN sys.indexes i ON i.object_id = ps.object_id
AND i.index_id = ps.index_idJOIN sys.objects so ON i.object_id = so.object_id
WHERE so.type = 'U'
GROUP BY so.schema_id
ORDER BY OBJECT_SCHEMA_NAME(so.schema_id)
,SizeInMB DESC