- Find all the foreign keys by executing below query.
SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id; - To find/drop a foreign key, use below query.
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command nvarchar(1000)
set @table_name = N'<tableName>'
set @col_name = N'<columnName>'
select @command = 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' drop constraint ' + f.name from sys.foreign_keys f, sys.foreign_key_columns fc where f.object_id = fc.constraint_object_id and OBJECT_NAME(f.parent_object_id) = @table_name and COL_NAME(fc.parent_object_id, fc.parent_column_id) = @col_name;
print @Command
execute (@Command)
When you encounter the error ORA-28001: the password has expired, execute below queries to resolve this. export ORACLE_SID=< databaseName> sqlplus / as SYSDBA SQL> alter profile default limit password_life_time unlimited; SQL> alter user <userName> identified by <password>; SQL> commit; SQL> exit;
Comments
Post a Comment