Belirli bir kolon adını bütün veri tabanındaki tablolarda aramak için:
(find column name in database tables)
SELECT c.name AS 'Kolon'
,t.name AS 'Tablo'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%ARANACAK%'
ORDER BY t.name
,c.name;
Tablo ve Viewlerde aramak için:
(find column name in database tables and views)
SELECT COLUMN_NAME AS 'Kolon'
,TABLE_NAME AS 'Tablo'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ARANACAK%'
ORDER BY Tablo
,Kolon;
Tekrar eden satırları silmek
(remove duplicate rows)
CREATE TABLE Table1([col1] varchar(5), [col2] int, [col3] int, [col4] int, [col5] int, [col6] int, [col7] int);
INSERT INTO Table1
([col1], [col2], [col3], [col4], [col5], [col6], [col7])
VALUES
('john', 1, 1, 1, 1, 1, 1),
('john', 1, 1, 1, 1, 1, 1),
('sally', 2, 2, 2, 2, 2, 2),
('sally', 2, 2, 2, 2, 2, 2),
('sally', 2, 2, 2, 3, 2, 2)
;
SELECT * FROM dbo.Table1
;WITH CTE AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
RN = ROW_NUMBER()OVER(PARTITION BY col1 , col2 , col3 , col4 , col5 , col6 , col7 ORDER BY col1)
FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1
SELECT * FROM dbo.Table1
İki tarih arasını ay bazlı olarak listelemek
(get all months and years between two dates)
DEMO
DECLARE
@start DATE = '01.01.2018'
, @end DATE = '09.09.2018'
;WITH cte AS
(
SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)
UNION ALL
SELECT DATEADD(MONTH, 1, dt)
FROM cte
WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)
)
SELECT AY = MONTH(DT), YIL = YEAR(DT)
INTO #Dates FROM cte
option (MaxRecursion 0);
SELECT * FROM #Dates
DROP TABLE #Dates