PostgreSQL : dependencies on a schema

deletedependenciespostgresqlschema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a way to list all the dependencies in the rest of the database ?

I have tried DROP SCHEMA mySchema ; but the given list of dependencies is partial…

Many thanks for help !

Best Answer

You can use pg_depend table to resolve that :

select objid::regclass 
from pg_depend 
where refobjid = (select oid
                  from pg_namespace 
                  where nspname='mySchema');

Related Solutions

Sql-server – What permissions are needed to view dependencies

The 2012 Books Online topic "Get Information About a View" states that the permissions required for this specific task are:

VIEW DEFINITION permission on the database; and
SELECT permission on sys.sql_expression_dependencies

Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; object-level VIEW DEFINITION and SELECT on the DMV will not work (you will receive an empty result set).

Example:

USE Sandpit;
GO
CREATE VIEW dbo.V1 AS SELECT 1 AS const;
GO
CREATE VIEW dbo.V2 AS SELECT v.const FROM dbo.V1 AS v;
GO
CREATE USER Bob WITHOUT LOGIN;

-- These two permissions are not sufficent
GRANT VIEW DEFINITION ON OBJECT::V1 TO Bob;
GRANT VIEW DEFINITION ON OBJECT::V2 TO Bob;

-- This one permission is enough (covers the two above)
GRANT VIEW DEFINITION ON DATABASE::Sandpit TO Bob;

-- Also required
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO Bob;
GO
EXECUTE AS USER = 'Bob';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V1', N'V'));
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V2', N'V'));

-- Show DMV info for the test views
SELECT
    referencing_object_name = OBJECT_NAME(sed.referencing_id),
    sed.referencing_class_desc,
    sed.is_schema_bound_reference,
    sed.referenced_class_desc,
    sed.referenced_schema_name,
    sed.referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE
    sed.referenced_id = OBJECT_ID(N'dbo.V1', N'V');
GO
REVERT;
GO
DROP USER Bob;
DROP VIEW dbo.V1, dbo.V2;

Regarding sp_depends: that requires only membership of the public role but is deprecated:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

SQL Server Schema Ownership – How to Transfer Ownership of dbo Schema

Slightly different approach would be to give the schema back to the original owner/schema, instead of to dbo:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;

Best Answer

Related Solutions

Sql-server – What permissions are needed to view dependencies

SQL Server Schema Ownership – How to Transfer Ownership of dbo Schema

Related Question