GoGreen PC TuneUp™
Learn More

Insta-Install™
this is how we code sign our software and ssl encrypt our websites from our affiliate partner SSL.com

myBlog

myBlog Home

Back to Blog MTNCOMP


SQL Samples that come from 2016 testing for Randonmess

Published: Tuesday, October 15, 2019 written by Drew Jackson
View Count: 20
Keywords: SQL, Samples, Stored Procedures



 
This is just a sample test SQL for you to play with...
 

select round((rand() * 1 / 3) * 100000,0)


create database test1

use master

drop database test1

use test1

UPDATE `wp_users` SET `user_pass`= MD5('yourpassword') WHERE `user_login`='yourusername'

'openSQL on mariadb
select MD5('Microsoft')
select MD5('raj2son2')

$P$BjpOl7juKVLLHmPGUfZKDwIdi.KXF1.


'microsoft sql on sqlserver
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'Microsoft')), 3, 32)
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'raj2son2')), 3, 32)
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'raj2son249')), 3, 32)
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', '$Bigcity25149')), 3, 32)


dbcc checkdb
dbcc checkalloc
dbcc checkcatalog

dbcc checktable (v)

select datediff(d, getdate(), '6/16/2016')

sp_tables

select newid()

drop table v

select rand()

update v set currency1 = rand()
update v set currency1 = ABS(CHECKSUM(NEWID())) % 365 where currency1 is null
update v set currency2 = ABS(CHECKSUM(NEWID())) % 10000002

select ABS(CHECKSUM(NEWID())) % 10000001
select * from v
select count(*) from v
select currency1, count(*) from v group by currency1 order by 2 desc

create table v (
    row_id integer identity(1,1),
    text1 nvarchar(4000),
    text2 nvarchar(4000),
    float1 float,
    float2 float,
    currency1 money,
    currency2 money,
    integer1 integer,
    integer2 integer,
    datetime1 datetime default getdate(),
    datetime2 datetime default getdate()
)

set rowcount 50
set rowcount 0

delete  from v

select * from v

declare @@loop integer
select @@loop = 1
while @@loop < 100
    begin
        insert into v (float1) select rand()
    select @@loop = @@loop + 1
    end

create procedure proc_v as
-- alter procedure proc_v as
declare @loop integer
select @loop = 1
while @loop < 100000
    begin
        --insert into v (float1) select rand()
        --select round((rand() * 1 / 3) * 100000,0)
        insert into v (float1) select round((rand() * @loop / 3) * 1000000,0)
    select @loop = @loop + 1
    end

proc_v

delete  from v
select * from v
select float1, count(*) from v GROUP BY float1 ORDER BY 2 DESC







SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[v](
    [row_id] [int] IDENTITY(1,1) NOT NULL,
    [float1] [float] NULL,
    [float2] [float] NULL,
    [text1] [nvarchar](4000) NULL,
    [text2] [nvarchar](4000) NULL
) ON [PRIMARY]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
    [row_id] [int] IDENTITY(1,1) NOT NULL,
    [text1] [varchar](25) NULL,
    [text2] [varchar](255) NULL,
    [memo1] [image] NULL,
    [memo2] [image] NULL,
    [dollar1] [money] NULL,
    [dollar2] [money] NULL,
    [float1] [float] NULL,
    [float2] [float] NULL,
    [integer1] [int] NULL,
    [integer2] [int] NULL,
    [datetime1] [datetime] NULL,
    [datetime2] [datetime] NULL,
    [datetime3] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p] as
 create procedure p as  
declare @l as integer
select @l = 0
while @l < 10000
    begin
    insert into v (float1, float2) select rand(), rand()
    select @l = @l + 1
    end

    select * from v

    p

/* www.devx.com/DevX/tip-delete-data-safely-with-sql.html */

CREATE PROCEDURE sp_DeleteSafely AS

DECLARE @Table SYSNAME
DECLARE @Clause VARCHAR(MAX)
DECLARE @DeleteCount BIGINT
DECLARE @ActionCount BIGINT

BEGIN

DECLARE @sql VARCHAR(MAX)

BEGIN TRANSACTION

SELECT @sql = 'DELETE FROM ' + @Table + ' ' + @Clause
EXECUTE(@sql)

SELECT @ActionCount = @@ROWCOUNT

    IF @ActionCount = @DeleteCount

    BEGIN

        PRINT CAST(@ActionCount AS VARCHAR) + ' Rows Deleted.'
        COMMIT TRANSACTION

    END
    
    ELSE

    BEGIN
        PRINT 'Would Have Deleted ' + CAST(@ActionCount AS VARCHAR)
        ROLLBACK TRANSACTION

        SELECT @ActionCount = 0
    END
END
GO

Usage:

DECLARE @ActionCount BIGINT
EXEC sp_DeleteSafely 'TableName','WHERE CONDITION', 1, @ActionCount OUTPUT

more to come...
 

if you found this article helpful, consider contributing $10, 20 or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.

© 2019 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.