Structured Query Language Script code
structured-query-language-script-code

Structured Query Language Script code

SQL

SQL is a standard language for storing, manipulating and retrieving data in databases.

Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

SQL SCRIPTS

To find Objects’ dependencies, use the code below.

With CTE as
(
 SELECT ReferencedObjectType = o2.type, 
        --ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
           --ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
     ReferencingObjectType = o1.type

 FROM   sys.sql_expression_dependencies ed
     INNER JOIN  sys.objects o1
    ON ed.referencing_id = o1.object_id
     INNER JOIN sys.objects o2
    ON ed.referenced_id = o2.object_id
 --WHERE o1.type in ('P','TR','V', 'TF')  WHERE o1.type in ('P','V')
)
Select * from CTE where ReferencedObjectType='U' 
order by ReferencedObject,ReferencingObjectType

Database Size Growth

Script Language and Platform: SQL Serve

DECLARE @GROWTHDAYS int
SET  @GROWTHDAYS = 7
SELECT DISTINCT TOP 10
(SELECT SD.IPAddress
FROM  ServersDetails SD
WHERE SD.ServerID = dl.ServerID) IPAddress, dl.DatabaseName, dl.DataDrive, ((SELECT TOP 1 dl1.DataSizeInMB  FROM DatabasesList dl1

WHERE CAST(dl1.UpdatedDate AS DATE) = (SELECT CAST(MAX(LS.UpdatedDate) AS date) 
	FROM  DatabasesList LS WHERE ls.ServerID = dl.ServerID AND ls.DatabaseName = dl.DatabaseName)  
	AND dl1.DatabaseName = dl.DatabaseName AND dl1.ServerID = dl.ServerID) - (SELECT TOP 1 dl1.DataSizeInMB FROM  DatabasesList dl1 
	WHERE  /*CAST(dl1.UpdatedDate AS DATE) = (SELECT CAST(MIN(LS.UpdatedDate) as date) 
	FROM DatabasesList LS where ls.ServerID=dl.ServerID and ls.DatabaseName=dl.DatabaseName) */ CAST(dl1.UpdatedDate AS DATE) >= (SELECT CAST(MAX (LS.UpdatedDate - @GROWTHDAYS) AS date) 
	FROM  DatabasesList LS WHERE ls.ServerID = dl.ServerID AND ls.DatabaseName = dl.DatabaseName) AND dl1.DatabaseName = dl.DatabaseName AND dl1.ServerID = dl.ServerID)) GrowthMBInLastSevenDays  
	FROM (SELECT  dlmain.DatabaseName, dlmain.DataDrive, dlmain.LogDrive, dlmain.ServerID  FROM  DatabasesList dlmain  GROUP BY dlmain.DatabaseName, dlmain.DataDrive, dlmain.LogDrive, dlmain.ServerID) dl 
ORDER

Monitor CPU Usage Information

Script Language and Platform: Oracle

This script will monitor CPU Usage Information.

select  
  ss.username, 
  se.SID, 
  VALUE/100 cpu_usage_seconds from v$session ss,  
  v$sesstat se,  
  v$statname sn 
where 
  se.STATISTIC# = sn.STATISTIC# and 
  NAME like '%CPU used by this session%' and 
  se.SID = ss.SID and  
  ss.status='ACTIVE' and  
  ss.username is not null order by VALUE desc;

User Login Permission Detail

>Script Language and Platform: SQL Server

CREATE proc [dbo].[DBASP_ALL_UserPermission]
AS
DECLARE @strSQL nvarchar(2000),
@dbname nvarchar(256)
IF OBJECT_ID('tempdb..#DBUsers') IS NOT NULL DROP TABLE #DBUsers
CREATE table #DBUsers 
(
DBname varchar (256),  
LoginName varchar(100),  
DBUserName varchar(100),           
[DBRole] varchar (100),     
PrincipalType  varchar(100), 
PermissionName  varchar(100) ,
ObjectType varchar(50),  
Objectname varchar(100), 
Columnname varchar(100)
)  
DECLARE listdbs Cursor
FOR
SELECT name from master.dbo.sysdatabases
WHERE  name not in ('master', 'model', 'msdb', 'tempdb')
OPEN listdbs
FETCH next
     FROM  listdbs into @dbname    
     WHILE @@fetch_status = 0
     BEGIN   
     SELECT @strSQL =                      
    '
     Use ['+ @dbname+'] ;
     SELECT 
      DB_name()
     ,sp.name 
     ,dp.name    
     ,dp2.name
     ,dp.type_desc
     ,perm.permission_name
     , objectType = case perm.class
             WHEN 1 THEN obj.type_desc
                      ELSE perm.class_desc
     END
     ,objectName = case perm.class
              when 1 then Object_name(perm.major_id)
                    when 3 then schem.name 
                             when 4 then imp.name
     END
                     , col.name
     FROM
     sys.database_role_members drm
     RIGHT JOIN  sys.database_principals dp
     on dp.principal_id = drm.member_principal_id
     LEFT JOIN sys.database_principals dp2
     on dp2.principal_id = drm.role_principal_id
     FULL JOIN sys.server_principals sp 
     ON dp.[sid] = sp.[sid] 
     LEFT JOIN sys.database_permissions perm 
     ON perm.[grantee_principal_id] = dp.[principal_id]
     LEFT JOIN sys.columns col 
     ON col.[object_id] = perm.major_id 
     AND col.[column_id] = perm.[minor_id] 
     LEFT JOIN sys.objects obj 
     ON perm.[major_id] = obj.[object_id] 
     LEFT JOIN sys.schemas schem 
     ON schem.[schema_id] = perm.[major_id] 
     LEFT JOIN sys.database_principals imp 
     ON imp.[principal_id] = perm.[major_id] 
     WHERE dp.name not in (''sys'' , ''information_schema'' , ''guest'', ''public'')
     ORDER by sp.name
    '
    INSERT into #DBUsers
    EXEC (@strSQL)
    FETCH NEXT
    FROM listdbs into @dbname
    END
    CLOSE listdbs
    DEALLOCATE listdbs
    SELECT * from #DBUsers 

    GO

This Post Has One Comment

Leave a Reply