Script: List all foreign keys in a database

SELECT

o1.name AS FK_table,

c1.name AS FK_column,

fk.name AS FK_name,

o2.name AS PK_table,

c2.name AS PK_column,

pk.name AS PK_name,

fk.delete_referential_action_desc AS Delete_Action,

fk.update_referential_action_desc AS Update_Action

FROM sys.objects o1

INNER JOIN sys.foreign_keys fk

ON o1.object_id = fk.parent_object_id

INNER JOIN sys.foreign_key_columns fkc

ON fk.object_id = fkc.constraint_object_id

INNER JOIN sys.columns c1

ON fkc.parent_object_id = c1.object_id

AND fkc.parent_column_id = c1.column_id

INNER JOIN sys.columns c2

ON fkc.referenced_object_id = c2.object_id

AND fkc.referenced_column_id = c2.column_id

INNER JOIN sys.objects o2

ON fk.referenced_object_id = o2.object_id

INNER JOIN sys.key_constraints pk

ON fk.referenced_object_id = pk.parent_object_id

AND fk.key_index_id = pk.unique_index_id

ORDER BY o1.name, o2.name, fkc.constraint_column_id

Permission: To list the permissions on a schema

--the schema name is testschema
select pr.permission_name as permissionName,s.[name] as schemaname,s.[schema_id], p.[name] as principal_name, p.principal_id as pricipalID
from sys.schemas s
inner join sys.database_permissions pr
on s.schema_id = pr.major_Id
inner join sys.database_principals p
on pr.grantee_principal_id = p.principal_id
where s.[name] = 'testschema'

Schema: change schema of a user

The schema of a user was associated to a different schema instead of the default schema dbo. to check the user who do not have the default schema as dbo
select 'ALTER USER [' + name + '] with default_schema = [dbo]'
from sys.database_principals
where type in ('U')
and default_schema_name not in ('dbo')
--the above lists and generates a alter script
ALTER USER [Dom1\user1] with default_schema = [dbo]


--change the schema of the stored procs
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p
INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'CHANGE_ME_Username'