Last active
February 25, 2022 15:10
-
-
Save lockworld/f60ff2464753de40a98a5778412a5eb1 to your computer and use it in GitHub Desktop.
Handy T-SQL Commands
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
If you want to add a column with random numbers as part of an INSERT statement, you can't use the RAND() function becuase you will always get the same number. | |
Instead, use the following code, which will return a random number between 0 and one less than the number following the modulus operator | |
*/ | |
SELECT ABS(CHECKSUM(NewId())) % 6 -- this will return a random number between 0 and 5 | |
SELECT (ABS(CHECKSUM(NewId())) % 5) + 1 -- this will return a random number between 1 and 5 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Problems with collation affect equality | |
ERROR: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. | |
Reference: https://stackoverflow.com/questions/1404880/sql-collation-conflict-when-comparing-to-a-column-in-a-temp-table | |
*/ | |
SELECT * | |
FROM my_table | |
INNER JOIN | |
@TempTable tem | |
ON my_table.id = temp.id COLLATE SQL_Latin1_General_CP1_CI_AS | |
AND my_table.key = 'SOME STRING' | |
-- OR | |
CREATE TABLE @TempTable (ID NVARCHAR(255) COLLATE database_default) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select * | |
from INFORMATION_SCHEMA.COLUMNS | |
where TABLE_NAME='YourTableName' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*This converts {DateFiel} and {TimeField} to a DateTme format*/ | |
SELECT (DATEADD(s, {TimeField}, CAST({DateField} AS DateTime))) AS Timestamp, * FROM MyTable |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Count how many times a value is repeated in a table */ | |
SELECT Key1, | |
COUNT(Key1) AS Total, | |
(SELECT COUNT(*) FROM MyTable) AS Records, | |
(COUNT(Key1) * 1.0 / (SELECT COUNT(*) FROM MyTable) * 1.0) AS Pct | |
FROM MyTable | |
GROUP BY Key1 | |
ORDER BY (COUNT(Key1) * 1.0 / (SELECT COUNT(*) FROM MyTable) * 1.0) DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Convert a date and a time into a datetime: | |
--where @SysTime and @Date are the db fields you want to convert | |
CAST(DATEADD(ms, @SysTime * 1000, 0) AS datetime) + CAST(@Date AS datetime) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--https://stackoverflow.com/a/32980415 | |
select t.name, s.row_count | |
from sys.tables t | |
join sys.dm_db_partition_stats s | |
ON t.object_id = s.object_id | |
and t.type_desc = 'USER_TABLE' | |
and t.name not like '%dss%' | |
and s.index_id = 1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
db.name AS DBName, | |
type_desc AS FileType, | |
Physical_Name AS Location | |
FROM | |
sys.master_files mf | |
INNER JOIN | |
sys.databases db ON db.database_id = mf.database_id |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @sp_who2 table | |
( | |
[SPID] varchar(20), | |
[Status] varchar(50), | |
[Login] varchar(100), | |
[HostName] varchar(100), | |
[BlockedBy] varchar(20), | |
[DBName] varchar(50), | |
[Command] varchar (100), | |
[CPUTime] varchar(25), | |
[DiskIO] varchar(25), | |
[LastBatch] varchar(20), | |
[ProgramName] varchar(100), | |
[SPID2] varchar(20), | |
[REQUESTID] varchar(20) | |
) | |
INSERT INTO @sp_who2 | |
exec sp_who2 | |
SELECT GETDATE()as RunDateTime, * from @sp_who2 | |
WHERE RTRIM([Status]) in ('RUNNABLE', 'SUSPENDED') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
CASE | |
WHEN ISNUMERIC(varchar_field)=1 | |
THEN str(CAST(varchar_field AS float),30,30) | |
ELSE varchar_field | |
END AS numeric_sorted_varchar_field | |
--EXAMPLE | |
-- Not fully functional...problems with float conversion precision | |
DECLARE @is TABLE( | |
val varchar(30) | |
) | |
INSERT INTO @is | |
(val) | |
VALUES | |
('0.00'), ('0'), ('0.00'), ('0.10'), ('0.15'), ('0.2'), ('1'), ('1.0'), ('1.1'), ('1.15'), ('1.2'), ('1.20'), ('15.0'), ('15'), ('15.1123'), ('20'), ('21.0'), ('22.351'), ('3'), ('30.12'),(''),('low'),('high'),('1132262.1') | |
SELECT | |
CAST( | |
CASE | |
WHEN ISNUMERIC(val)=1 | |
THEN STR(CAST(val AS float), 30,30) | |
ELSE val | |
END AS VARCHAR(30)) AS i | |
FROM @is | |
ORDER BY i |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Read: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server | |
*/ | |
USE master | |
GO | |
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL | |
DROP PROCEDURE sp_hexadecimal | |
GO | |
CREATE PROCEDURE sp_hexadecimal | |
@binvalue varbinary(256), | |
@hexvalue varchar (514) OUTPUT | |
AS | |
DECLARE @charvalue varchar (514) | |
DECLARE @i int | |
DECLARE @length int | |
DECLARE @hexstring char(16) | |
SELECT @charvalue = '0x' | |
SELECT @i = 1 | |
SELECT @length = DATALENGTH (@binvalue) | |
SELECT @hexstring = '0123456789ABCDEF' | |
WHILE (@i <= @length) | |
BEGIN | |
DECLARE @tempint int | |
DECLARE @firstint int | |
DECLARE @secondint int | |
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) | |
SELECT @firstint = FLOOR(@tempint/16) | |
SELECT @secondint = @tempint - (@firstint*16) | |
SELECT @charvalue = @charvalue + | |
SUBSTRING(@hexstring, @firstint+1, 1) + | |
SUBSTRING(@hexstring, @secondint+1, 1) | |
SELECT @i = @i + 1 | |
END | |
SELECT @hexvalue = @charvalue | |
GO | |
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL | |
DROP PROCEDURE sp_help_revlogin | |
GO | |
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS | |
DECLARE @name sysname | |
DECLARE @type varchar (1) | |
DECLARE @hasaccess int | |
DECLARE @denylogin int | |
DECLARE @is_disabled int | |
DECLARE @PWD_varbinary varbinary (256) | |
DECLARE @PWD_string varchar (514) | |
DECLARE @SID_varbinary varbinary (85) | |
DECLARE @SID_string varchar (514) | |
DECLARE @tmpstr varchar (1024) | |
DECLARE @is_policy_checked varchar (3) | |
DECLARE @is_expiration_checked varchar (3) | |
DECLARE @defaultdb sysname | |
IF (@login_name IS NULL) | |
DECLARE login_curs CURSOR FOR | |
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM | |
sys.server_principals p LEFT JOIN sys.syslogins l | |
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' | |
ELSE | |
DECLARE login_curs CURSOR FOR | |
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM | |
sys.server_principals p LEFT JOIN sys.syslogins l | |
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name | |
OPEN login_curs | |
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin | |
IF (@@fetch_status = -1) | |
BEGIN | |
PRINT 'No login(s) found.' | |
CLOSE login_curs | |
DEALLOCATE login_curs | |
RETURN -1 | |
END | |
SET @tmpstr = '/* sp_help_revlogin script ' | |
PRINT @tmpstr | |
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' | |
PRINT @tmpstr | |
PRINT '' | |
WHILE (@@fetch_status <> -1) | |
BEGIN | |
IF (@@fetch_status <> -2) | |
BEGIN | |
PRINT '' | |
SET @tmpstr = '-- Login: ' + @name | |
PRINT @tmpstr | |
IF (@type IN ( 'G', 'U')) | |
BEGIN -- NT authenticated account/group | |
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' | |
END | |
ELSE BEGIN -- SQL Server authentication | |
-- obtain password and sid | |
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) | |
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT | |
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT | |
-- obtain password policy state | |
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name | |
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name | |
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' | |
IF ( @is_policy_checked IS NOT NULL ) | |
BEGIN | |
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked | |
END | |
IF ( @is_expiration_checked IS NOT NULL ) | |
BEGIN | |
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked | |
END | |
END | |
IF (@denylogin = 1) | |
BEGIN -- login is denied access | |
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) | |
END | |
ELSE IF (@hasaccess = 0) | |
BEGIN -- login exists but does not have access | |
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) | |
END | |
IF (@is_disabled = 1) | |
BEGIN -- login is disabled | |
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' | |
END | |
PRINT @tmpstr | |
END | |
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin | |
END | |
CLOSE login_curs | |
DEALLOCATE login_curs | |
RETURN 0 | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Declare variables to hold values for each iteration | |
DECLARE @field1 nvarchar(50), @field2 nvarchar(50), @field3 nvarchar(50) | |
--Declare cursor for a select statement | |
DECLARE cur CURSOR FOR | |
SELECT [Field1] | |
,[Field2] | |
,[Field3] | |
FROM [Table1] | |
WHERE [Field4]='SomeValue' | |
--Open cursor for use | |
OPEN cur | |
--Get the first line from the cursor and place the values into declared variables | |
FETCH NEXT FROM cur INTO @field1, @field2, @field3 | |
WHILE (@@fetch_status <> -1) | |
BEGIN | |
--Perform action based on current line's variables | |
INSERT INTO [Table2] | |
(Field1, Field2, Field3, Field4) | |
VALUES | |
(@field1, @field2, @field3, 'OtherValue') | |
--!IMPORTANT! | |
--Iterate to the next line in the cursor | |
FETCH NEXT FROM cur INTO @field1, @field2, @field3 | |
END | |
--Close and deallocate cursor | |
CLOSE cur | |
DEALLOCATE cur |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment