SQL Samples that come from 2016 testing for Randonmess

 

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...