Category: SQL

— 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’;