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