Полезные Mssql Скрипты.

<?php
$db_host    = 'Ваш хост';
$db_user    = 'sa';
$db_user    = 'Пароль к бд';

function get_time($god, $month)
{
    $time=$god.$month."01";
    return $time;
}

echo "<center><form>Введите год: <input type='text' name='god'>&nbsp;&nbsp;&nbsp;Введите месяц: <input type='text' name='month'> (пример 05)&nbsp;&nbsp;&nbsp;<input type=submit value='Очистить'></form></center>";

if(@$_GET['god']<>'' && @$_GET['month']<>'')
{
    mssql_connect($db_host, $db_user, $db_user);
    mssql_select_db('lin2world');

    $sql=mssql_query("select * from user_data where login<'".get_time(@$_GET['god'], @$_GET['month'])."'");

    if(mssql_num_rows($sql)>0)
    {
        while($top=mssql_fetch_array($sql))
        {
            if($top['pledge_id']=='0')
            {
                echo "<br>Удален чар с ником: ".$top['char_name']." . Последний раз заходил за чара: ".$top['login'];
                mssql_query("delete from user_data where char_id='".$top['char_id']."'");
                mssql_query("delete from user_item where char_id='".$top['char_id']."' and warehouse<>'2'");
                mssql_query("delete from user_skill where char_id='".$top['char_id']."'");
                mssql_query("delete from user_warehouse where char_id='".$top['char_id']."'");
                mssql_query("delete from user_surrender where char_id='".$top['char_id']."'");
                mssql_query("delete from user_subjob where char_id='".$top['char_id']."'");
                mssql_query("delete from user_sociality where char_id='".$top['char_id']."'");
                mssql_query("delete from user_skill_old where char_id='".$top['char_id']."'");
                mssql_query("delete from user_recipe where char_id='".$top['char_id']."'");
                mssql_query("delete from user_punish where char_id='".$top['char_id']."'");
                mssql_query("delete from user_nobless where char_id='".$top['char_id']."'");
                mssql_query("delete from user_newbie where char_id='".$top['char_id']."'");
                mssql_query("delete from user_macro where char_id='".$top['char_id']."'");
                mssql_query("delete from user_log where char_id='".$top['char_id']."'");
                mssql_query("delete from user_journal where char_id='".$top['char_id']."'");
                mssql_query("delete from user_history where char_id='".$top['char_id']."'");
                mssql_query("delete from user_friend where char_id='".$top['char_id']."'");
                mssql_query("delete from user_deleted where char_id='".$top['char_id']."'");
            }
            else
            {
                $ccc=mssql_query("select * from pledge where ruler_id='".$top['char_id']."'");

                if(mssql_num_rows($ccc)==0)
                {
                    echo "<br>Удален чар с ником: ".$top['char_name']." . Последний раз заходил за чара: ".$top['login'];
                    mssql_query("delete from user_data where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_item where char_id='".$top['char_id']."' and warehouse<>'2'");
                    mssql_query("delete from user_skill where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_warehouse where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_surrender where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_subjob where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_sociality where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_skill_old where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_recipe where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_punish where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_nobless where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_newbie where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_macro where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_log where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_journal where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_history where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_friend where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_deleted where char_id='".$top['char_id']."'");
                }
                else
                {
                    $bbb=mssql_fetch_array($ccc);
                    echo "<br>Удален чар с ником: ".$top['char_name']." . Последний раз заходил за чара: ".$top['login']." . А так же удален клан: ".$bbb['name'];
                    mssql_query("update user_Data set pledge_id='0' where pledge_id='".$bbb['pledge_id']."'");
                    mssql_query("delete from pledge where ruler_id='".$top['char_id']."'");
                    mssql_query("delete from user_data where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_item where char_id='".$top['char_id']."' and warehouse<>'2'");
                    mssql_query("delete from user_skill where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_warehouse where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_surrender where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_subjob where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_sociality where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_skill_old where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_recipe where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_punish where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_nobless where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_newbie where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_macro where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_log where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_journal where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_history where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_friend where char_id='".$top['char_id']."'");
                    mssql_query("delete from user_deleted where char_id='".$top['char_id']."'");
                }
            }
        }

        echo "<br>Удаление закончили....";
    }
    else
    {
        echo "Удалять, то нечего ) ";
    }
}
else
{
    echo "<br>Пожалуйста укажите год/месяц";
}
?>

USE [lin2world]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

USE [lin2world]
GO
ALTER DATABASE [lin2world] SET RECOVERY SIMPLE
GO

USE [lin2world]
GO
CHECKPOINT
GO

USE [lin2world]
GO
BACKUP LOG [lin2world] WITH NO_LOG
GO

USE [lin2world]
GO
SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

USE [lin2world]
GO
DBCC SHRINKFILE (lin2world_log, 1)
GO

USE [lin2world]

use lin2world;

ALTER PROCEDURE dbo.lin_CreateChar
(  
@char_name NVARCHAR(24),  
@account_name NVARCHAR(24),  
@account_id INT,  
@pledge_id INT,  
@builder  TINYINT,  
@gender TINYINT,  
@race  TINYINT,  
@class  TINYINT,  
@world  SMALLINT,  
@xloc  INT,  
@yloc  INT,  
@zloc  INT,  
@HP  FLOAT,  
@MP  FLOAT,  
@SP  INT,  
@Exp  INT,  
@Lev  TINYINT,  
@align  SMALLINT,  
@PK  INT,  
@Duel  INT,  
@PKPardon  INT,  
@FaceIndex   INT = 0,  
@HairShapeIndex  INT = 0,  
@HairColorIndex  INT = 0  
)  
AS  
  
SET NOCOUNT ON  
  
SET @char_name = RTRIM(@char_name)  
DECLARE @char_id int  
SET @char_id = 0  
  

IF @char_name LIKE N' '  
BEGIN  
RAISERROR ('Character name has space : name = [%s]', 16, 1, @char_name)  
RETURN -1  
END  
  
-- check user_prohibit  
if exists(select char_name from user_prohibit (nolock) where char_name = @char_name)  
begin  
RAISERROR ('Character name is prohibited: name = [%s]', 16, 1, @char_name)  
RETURN -1  
end  
  
declare @user_prohibit_word nvarchar(20)  
select top 1 @user_prohibit_word = words from user_prohibit_word (nolock) where @char_name like '%' + words + '%'
if @user_prohibit_word is not null  
begin  
RAISERROR ('Character name has prohibited word: name = [%s], word[%s]', 16, 1, @char_name, @user_prohibit_word)  
RETURN -1  
end  
  
-- check reserved name  
declare @reserved_name nvarchar(50)  
declare @reserved_account_id int  
select top 1 @reserved_name = char_name, @reserved_account_id = account_id from user_name_reserved (nolock) where used = 0 and char_name = @char_name  
if not @reserved_name is null  
begin  
if not @reserved_account_id = @account_id  
begin  
  RAISERROR ('Character name is reserved by other player: name = [%s]', 16, 1, @char_name)  
  RETURN -1  
end  
end  
  
IF @race>4
BEGIN  
RAISERROR ('Race overflow : = [%s]', 16, 1, @char_name)  
RETURN -1  
END  

IF @race=0 and @class!=0 and @class!=10
BEGIN  
RAISERROR ('Class Overflow for Human: = [%s]', 16, 1, @class)  
RETURN -1  
END  

IF @race=1 and @class!=18 and @class!=25
BEGIN  
RAISERROR ('Class Overflow for Elf: = [%s]', 16, 1, @class)  
RETURN -1  
END  

IF @race=2 and @class!=31 and @class!=38
BEGIN  
RAISERROR ('Class Overflow for DE: = [%s]', 16, 1, @class)  
RETURN -1  
END  

IF @race=3 and @class!=44 and @class!=49
BEGIN  
RAISERROR ('Class Overflow for Orc: = [%s]', 16, 1, @class)  
RETURN -1  
END  

IF @race=4 and @class!=53
BEGIN  
RAISERROR ('Class Overflow for Dwarf: = [%s]', 16, 1, @class)  
RETURN -1  
END  

-- insert user_data  
INSERT INTO user_data  
( char_name, account_name, account_id, pledge_id, builder, gender, race, class, subjob0_class,
world, xloc, yloc, zloc, HP, MP, max_hp, max_mp, SP, Exp, Lev, align, PK, PKpardon, duel, create_date, face_index, hair_shape_index, hair_color_index )  
VALUES  
(@char_name, @account_name, @account_id, @pledge_id, @builder, @gender, @race, @class, @class,
@world, @xloc, @yloc, @zloc, @HP, @MP, @HP, @MP, 90950592, 931850677, 76, @align, @PK, @Duel, @PKPardon, GETDATE(), @FaceIndex, @HairShapeIndex, @HairColorIndex)  
  
IF (@@error = 0)  
BEGIN  
SET @char_id = @@IDENTITY  
INSERT INTO quest (char_id) VALUES (@char_id)  
END  
  
SELECT @char_id  
  
if @char_id > 0  
begin  
-- make user_history  
exec lin_InsertUserHistory @char_name, @char_id, 1, @account_name, NULL  
if not @reserved_name is null  
  update user_name_reserved set used = 1 where char_name = @reserved_name  
end
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ap_GPwd] @account varchar(14), @pwd binary(16) output
AS
--exec ap_GPwd_log @account

-- Created By darkangel
-- 2003.06.19
-- updated 2006.05.14 by KEMBL
-- set Account's auth logging
-- updated 2006.08.31 Logrus, Drone
-- added login counts per time check
-- update 2006.10.25 by Stpavel
-- added random generate password

DECLARE @RandPass varchar(32)
DECLARE @counter smallint
DECLARE @RandNum float
DECLARE @RandNumInt tinyint
DECLARE @CurrChar varchar(1)
DECLARE @ValidChar varchar(255)
SET @ValidChar = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'
DECLARE @ValidCharLength int
SET @ValidCharLength = len(@ValidChar)
SET @CurrChar = ''
SET @RandNum = 0
SET @RandNumInt = 0
SET @RandPass = '


SET NOCOUNT ON

if (SELECT COUNT(*) FROM lin2db..user_auth_log WHERE account=@account AND DATEDIFF(mi, dt, GETDATE()) < 16) > 4
OR
(SELECT COUNT(*) FROM lin2db..user_auth_log WHERE account=@account AND DATEDIFF(ss, dt, GETDATE()) < 3) > 0
BEGIN
INSERT INTO user_auth_log (account,success) VALUES (@account, 0)

SET @counter = 1;
WHILE @counter < (17)
BEGIN
SET @RandNum = Rand();
SET @RandNumInt = Convert(tinyint, ((@ValidCharLength - 1) * @RandNum + 1))

SELECT @CurrChar = SUBSTRING(@ValidChar, @RandNumInt, 1);
SET @counter = @counter + 1;
SET @RandPass = @RandPass + @CurrChar;
END

SET NOCOUNT OFF

SELECT @pwd=convert(binary(16),@RandPass);

END
ELSE
BEGIN
INSERT INTO user_auth_log (account,success) VALUES (@account, 1)
SET NOCOUNT OFF
SELECT @pwd=password FROM user_auth with (nolock) WHERE account=@account
END