[Oracle] Enable / Disable all constraints in DB

Disable Constraints:

BEGIN

FOR c IN

(SELECT c.owner, c.table_name, c.constraint_name

FROM user_constraints c, user_tables t

WHERE c.table_name = t.table_name

AND c.status = 'ENABLED'

ORDER BY c.constraint_type DESC)

LOOP

dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);

END LOOP;

END;

Enable Constraints:

BEGIN

FOR c IN

(SELECT c.owner, c.table_name, c.constraint_name

FROM user_constraints c, user_tables t

WHERE c.table_name = t.table_name

AND c.status = 'DISABLED'

ORDER BY c.constraint_type)

LOOP

dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);

END LOOP;

END;

[Oracle] Update Data in Table trough Joins

create table source_table

(source_id    number primary key

,s_col1       number

,s_col2       number

,s_col3       number

);



create table target_table

(target_id    number primary key

,t_col1       number

,t_col2       number

,t_col3       number

);



insert into source_table(source_id,s_col1,s_col2,s_col3) values (101,111,1.01,-1);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (102,222,2.02,-2);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (103,333,3.03,-3);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (104,444,4.04,-4);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (105,555,5.05,-5);



insert into target_table(target_id,t_col1,t_col2,t_col3) values (101,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (102,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (103,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (104,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (105,null,null,null);

select * from source_table;
select * from target_table;

update
(
   select t_col1,t_col2,t_col3,s_col1,s_col2,s_col3
   from   target_table t
         ,source_table s
   where  t.target_id = s.source_id
)
set t_col1 = s_col1
   ,t_col2 = s_col2
   ,t_col3 = s_col3
;
select * from source_table;
select * from target_table;

[T-SQL] How to Rename a Column Name or Table Name

This morning i needed to rename column name and table name itself , so tought to share it on blog.

The script for renaming any column :
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) :
sp_RENAME 'OldTableName' , 'NewTableName'

This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs'

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

[T-SQL] Get Biggest Table on Your Production DB

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_huge_tables' AND type = 'P')
DROP PROC sp_show_huge_tables
GO

CREATE PROC sp_show_huge_tables
(
@top int = NULL,
@include_system_tables bit = 0
)
AS

BEGIN
IF @top > 0
SET ROWCOUNT @top

SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))

) as a
ORDER BY [Total space used (MB)] DESC


SET ROWCOUNT 0
END

GO

GRANT EXEC ON sp_show_huge_tables TO Public