SQL HELP

check_before_use.sql

WHERE u.UserName LIKE '%[^a-zA-Z0-9]%'

get_tables_without_pk.sql

select schema_name(tab.schema_id) as [schema_name], 
    tab.[name] as table_name
from sys.tables tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
where pk.object_id is null
order by schema_name(tab.schema_id),
    tab.[name]

insert_random_value.sql

declare @minDate date = '2020-12-01'
declare @maxDate date = '2020-12-31' 
declare @counter int = 1

while(@counter<10)
begin

	insert into 
		TargetTable(Col1, Col2, Col3)
	values
		(
			(select TOP 1 UserId from Members with (nolock) where Active=1 ORDER BY NEWID()),
			RAND()*(600-4)+4, -- a double between 4-600
			dateadd(day,rand(checksum(newid()))*(1+datediff(day, @minDate, @maxDate)), @minDate)) --random date

	set @counter= @counter+1
end

non_clustered_index.sql

CREATE NONCLUSTERED INDEX <index_name>
ON <table_name>(<column_name> ASC/DESC)

remove_every_second_row.sql

delete T
from (
       select row_number() over(order by [Timestamp]) as rn
       from YourTable
       where SomeColumn = @SomeValue
     ) T
where T.rn % 2 = 0

rename_table.sql

EXEC sp_rename 'oldName', 'newName'

search_in_schema_and_server.sql

declare @keyword nvarchar(255) = 'sp'

SELECT 
	DISTINCT 
		o.name AS Object_Name,
		o.type_desc
FROM 
	sys.sql_modules m
		INNER JOIN
			sys.objects o
				ON m.object_id = o.object_id
WHERE m.definition Like '%'+@keyword+'%';

SELECT 
	s.step_id,
    j.[name],
    s.database_name,
    s.command
FROM   
	msdb.dbo.sysjobsteps AS s
		INNER JOIN msdb.dbo.sysjobs AS j ON  s.job_id = j.job_id
WHERE  
	s.command LIKE '%'+@keyword+'%';

text_search_in_jobs.sql

SELECT s.step_id,
       j.[name],
       s.database_name,
       s.command
FROM   msdb.dbo.sysjobsteps AS s
INNER JOIN msdb.dbo.sysjobs AS j ON  s.job_id = j.job_id
WHERE  s.command LIKE '%Stored_procedure%'

text_search_in_schema.sql

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE m.definition Like '%[ABD]%';

update_join.sql

UPDATE 
    T1
SET 
    T1.c1 = T2.c1
FROM 
    TABLEONE T1
    INNER JOIN TABLETWO T2 
        ON T1.ID=T2.ID
WHERE --OPTIONAL
    T1.C2 = 'SOMEVALUE';

where_is_the_column.sql

--helps find any col across all tables
--herhangi bir kolonu tüm tablolarda aramak için

DECLARE @COLUMN VARCHAR(max) = '%USER%' 

SELECT 
	C.TABLE_CATALOG [CATALOG], C.TABLE_SCHEMA [SCHEMA], C.TABLE_NAME [TABLE], C.COLUMN_NAME [COLUMN], 
	CASE WHEN V.TABLE_NAME IS NULL THEN 'TABLE' ELSE 'VIEW' END AS [TYPE],
	CONCAT(C.TABLE_CATALOG, '.', C.TABLE_SCHEMA, '.', C.TABLE_NAME, '.', C.COLUMN_NAME) AS ADRESS
FROM 
	INFORMATION_SCHEMA.COLUMNS C LEFT JOIN INFORMATION_SCHEMA.VIEWS V ON V.TABLE_NAME=C.TABLE_NAME
WHERE 
	COLUMN_NAME LIKE @COLUMN
ORDER BY 
	[CATALOG], [SCHEMA], [TABLE], [COLUMN], [TYPE]

where_is_the_table.sql

--helps find your table across schemas
--tablonuzun hangi şemada olduğunu bulmak için

SELECT
	CONCAT(S.name, '.', T.name) TableLocation,
	CONCAT('SELECT * FROM ',S.name, '.', T.name) SelectFrom
FROM
	sys.tables T
		JOIN sys.schemas S ON T.schema_id=S.schema_id
where 
	T.name like '%theTable%'