Author: Mustafa EL-Masry

Following Script will help us to find space utilized by every database object, this includes number of rows and size taken by every table.

DECLARE @projected_size nvarchar(2)
SET @projected_size = NULL

declare        @db_count    bigint,
            @loop_count    bigint,
            @sqlstat    varchar(7000),
            @sqlstat2    varchar(7000),
            @sqlstat3   varchar(7000),
            @sqlstat4   varchar(7000),
            @sqlstat5   varchar(7000),
            @db_name    varchar(150)

CREATE TABLE #tablestats
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    data_size            nvarchar(38),
    reserved_size        nvarchar(38),
    unused_size            nvarchar(38),
    index_size            nvarchar(38),
    primary_key_size    nvarchar(38),
    number_rows_table    bigint
)
CREATE TABLE #alltablestats
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    data_size            nvarchar(15),
    reserved_size        nvarchar(15),
    unused_size            nvarchar(15),
    index_size            nvarchar(15),
    primary_key_size    nvarchar(15),
    number_rows_table    bigint
)
CREATE TABLE #tabpage
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    page_size            dec(30,0),
    used_size            dec(30,0),
    index_size            dec(30,0)
)

CREATE TABLE #pkhold
(
    pk_id                bigint,
    pk_name              varchar(400),
    parent_id            bigint,
    pk_size                nvarchar(15)
)
CREATE TABLE #capacity
(
    tb_id                bigint identity,
    database_name        varchar(150),
    database_id            bigint
)

IF @projected_size  IS null or @projected_size = ‘0’
  SET @projected_size = ‘1’

SET @loop_count = 1

/*****************************************************************************
***get database names on server                                            ***
******************************************************************************/
INSERT INTO #capacity(database_name,database_id)
SELECT DISTINCT name,dbid
FROM master..sysdatabases

SELECT @db_count = count(*)
FROM #capacity

WHILE @loop_count <= @db_count
BEGIN
    SELECT @db_name = database_name
    FROM #capacity
    WHERE tb_id = @loop_count   

/*****************************************************************************
***get table name,id, and data size                                        ***
******************************************************************************/
    SET @sqlstat = ‘use ‘ +  @db_name   

    SET @sqlstat2 = ‘ insert into #tablestats(database_name,table_name,table_id)
             SELECT ”’+ @db_name + ”’,name, ID FROM SYSOBJECTS WHERE XTYPE =”U”’
    EXEC (@sqlstat + @sqlstat2)

    SET @sqlstat2 = ‘ insert into #tabpage(database_name,table_name,table_id,page_size,used_size)
             select distinct database_name,table_name, table_id, ”page size” = (SELECT isnull(SUM(DPAGES),0)
                  FROM SYSINDEXES
                WHERE SYSINDEXES.INDID < 2 AND
                                      SYSINDEXES.ID = #tablestats.table_id),’
    SET @sqlstat3 = ”’used size” = (SELECT ISNULL(SUM(USED),0)
                FROM SYSINDEXES
                WHERE SYSINDEXES.INDID = 255 AND
                                      SYSINDEXES.ID = #tablestats.table_id)
               from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3)

    SET @sqlstat2 = ‘ update #tablestats set data_size = (select LTRIM(STR((page_size + used_size ) ‘
    SET @sqlstat3 = ‘* 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) from  #tabpage ‘
    SET @sqlstat4 = ‘where #tabpage.table_id = #tablestats.table_id and #tabpage.table_name = #tablestats.table_name) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get reserved size                                                       ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set reserved_size = (SELECT LTRIM(STR(cast(SUM(RESERVED) as bigint) ‘
    SET @sqlstat3 = ‘  * 8192 / 1024.*’+@projected_size+’,300,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
    SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get unused size                                                         ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set unused_size = (SELECT LTRIM(STR((cast(SUM(RESERVED) as bigint)- SUM(USED)) ‘
    SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
    SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get index size                                                          ***
******************************************************************************/
    DELETE
    FROM #tabpage

    SET @sqlstat2 = ‘ insert into #tabpage(table_id,page_size,used_size,index_size)select table_id, ‘
    SET @sqlstat3 = ‘ ”page size” = (SELECT SUM(DPAGES) FROM SYSINDEXES WHERE SYSINDEXES.INDID < 2 AND SYSINDEXES.ID = #tablestats.table_id), ‘
    SET @sqlstat4 = ‘ ”used size” = (SELECT SUM(convert(dec(15),USED)) FROM SYSINDEXES WHERE SYSINDEXES.INDID in (255) AND SYSINDEXES.ID = #tablestats.table_id), ‘
    SET @sqlstat5 = ‘ ”index_size” = (SELECT SUM(convert(dec(15),USED))FROM SYSINDEXES WHERE SYSINDEXES.INDID in (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats  ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4+@sqlstat5)

    SET @sqlstat2 = ‘ update #tablestats set INDEX_size = (SELECT LTRIM(STR((index_SIZE – (PAGE_SIZE + ISNULL(USED_SIZE,0))) ‘
    SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM  #TABPAGE ‘
    SET @sqlstat4 = ‘WHERE #tabPAGE.table_id = #tablestats.table_id) from #tablestats’
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get PK size                                                             ***
******************************************************************************/
    SET @sqlstat2 = ‘ INSERT INTO #PKHOLD(PK_ID,PK_NAME,PARENT_ID) SELECT SO.ID, ‘
    SET @sqlstat3 = ‘ SO.NAME,SO.PARENT_OBJ FROM SYSOBJECTS SO INNER JOIN #tablestats TS ON ‘
    SET @sqlstat4 = ‘ SO.PARENT_OBJ = TS.TABLE_ID WHERE SO.XTYPE = ”PK”’
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)

    UPDATE #tablestats
    SET primary_key_size = index_size
    FROM #PKHOLD  pk INNER JOIN #tablestats tb
              ON pk.PARENT_ID = tb.table_id
/*****************************************************************************
***get number rows                                                         ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set number_rows_table = rows*’+@projected_size
    SET @sqlstat3 = ‘ from sysindexes si inner join #tablestats tb on si.id = tb.table_id ‘
    SET @sqlstat4 = ‘ where si.indid < 2 ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)

     INSERT INTO #alltablestats(database_name,table_name,table_id,data_size,
                               reserved_size,unused_size,index_size,
                               primary_key_size,number_rows_table)
    SELECT database_name,table_name,table_id,data_size,
           reserved_size,unused_size,index_size,
           primary_key_size,number_rows_table
    FROM #tablestats
    ORDER BY database_name

     DELETE FROM #tablestats
     DELETE FROM #tabpage
    DELETE FROM #pkhold
    SET @loop_count = @loop_count + 1
 END
/*****************************************************************************
***return results to the screen                                            ***
******************************************************************************/
SELECT database_name,table_name,data_size,
       reserved_size,unused_size,index_size,
       primary_key_size,number_rows_table
FROM  #alltablestats
ORDER BY DATABASE_NAME,TABLE_NAME

use master
go
Dbcc sqlperf(logspace)
go

— ***********************************************************************************/
— Description  :    Script to check  how long the SQL Services were up  
—                        Following script will list down time for SQL Services are running and Current Status of SQL Server Agent too
— ***********************************************************************************/
SET NOCOUNT ON
DECLARE @crdate

 DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
   SELECT @crdate=crdate FROM sysdatabases WHERE NAME=’tempdb’
   SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
   IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
      SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
   ELSE
      SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
   PRINT ‘SQL Server “‘ + CONVERT(VARCHAR(20),SERVERPROPERTY(‘SERVERNAME’))+'” is Online for the past ‘+@hr+’ hours & ‘+@min+’ minutes’
   IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N’SQLAgent – Generic Refresher’)
      BEGIN
         PRINT ‘SQL Server is running but SQL Server Agent <<NOT>> running’
      END
   ELSE
      BEGIN
         PRINT ‘SQL Server and SQL Server Agent both are running’
END
go

 

now I will make trigger to prevent user to connect to sql server instance  in time between 5 pm to 7 am
and if he want to connect in this time i will save this transaction in database login in my SQL  server
i will used datatype sysname
is a built in datatype limited to 128 Unicode characters that, IIRC, is used primarily to store object names when creating scripts. It’s value cannot be NULL (sysname = nvarchar(128) not null)

— Create Login
Create Login Elmasry WITH PASSWORD = ‘egypt’
GO
Create Login Elmasry WITH PASSWORD = ‘egypt’
GO
—Create Database Login_info
CREATE DATABASE Login_Info
GO
USE Login_Info
GO
CREATE TABLE dbo.restrictedlogons
(loginid sysname not null, timestamp datetime not null)
go
use master
go
–Create Trigger
create trigger Prevent_USer_Login
on all server
for logon
as
begin
if original_login () = ‘Elmasry’ and
datepart(hh,getdate()) between 17 and 24
begin
rollback
insert  Login_Info.dbo.restrictedlogons
(loginid,timestamp)
values (original_login(),getdate())
end
end
GO
–Drop User Name
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N’Elmasry’)
DROP USER Elmasry
Go
—Drop Database
drop database Login_Info
Go
—Disable Trigger
USE Mastre;
GO
DISABLE TRIGGER Prevent_USer_Login ON All Server;
GO
—Drop Trigger
USE MASTER;
GO
DROP TRIGGER Prevent_USer_Login ON ALL SERVER ;
GO