Microsoft SQL Server

T SQL Query Ultilities

— Find all Column not in Table

select name from sys.tables
where name not in
(
SELECT t.name 
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE 'IsDeleted'
)

— Deleting all duplicate rows but keeping one [duplicate]

WITH cte AS (
  SELECT FirstName, 
     row_number() OVER(PARTITION BY FirstName ORDER BY FirstName) AS [rn]
  FROM Client
)
DELETE cte WHERE [rn] > 1

— Adding a column to all user tables in T-SQL

exec sp_msforeachtable 'alter table ? add IsDeleted bit not null default 0';

--- Adding a column to all user table if not exists
EXEC sp_msforeachtable '
if not exists (select name from sys.tables
where name not in(SELECT t.name FROM sys.columns c
				JOIN sys.tables t ON c.object_id = t.object_id
				WHERE c.name LIKE ''IsDeleted'')
) 
begin
    ALTER TABLE ? ADD IsDeleted bit NOT NULL DEFAULT 0;
end';

 

Published by

tuanitpro

Tôi là Lê Thanh Tuấn, và tôi chia sẻ những điều mình cho rằng nó là thú vị, hay giúp ích cho bạn!

Leave a Reply