ORACLE AND MSSQLSERVER

SQL SERVER – 2005 List All Tables of Database This is very simple and can be achieved using system table sys.tables . USE YourDBName GO SELECT * FROM sys.Tables GO

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable...

Duplicate Records SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

USE AdventureWorks GO DECLARE @OrderBy VARCHAR(10) DECLARE @OrderByDirection VARCHAR(1) SET @OrderBy = 'State' ----Other options Postal for PostalCode, ---- State for StateProvinceID, City for City SET @OrderByDirection = 'D' ----Other options A for ascending,...

Backup Before Deleting Databas USE MSDB GO SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, CASE msdb..backupset.TYPE WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Differential'...

SQL SERVER – How to Identify Locked Table in SQL Server? Here is a quick script which will help users to identify locked tables in the SQL Server. SELECT OBJECT_NAME(p.OBJECT_ID) AS TableName, resource_type, resource_description FROM sys.dm_tran_locks...

DDL Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE...

How to fix tempdb Full issue ? 1) Since tempdb recovery model is simple , Tempdb log will not grow drastically unless open transactions are there if so we will kill that spid and then shrink it 2) If mdf file is full we can shrink that but sometimes we...

Troubleshooting Log Shipping: • 1) Jobs disabled can be a cause for LS failure. • 2) Backup Share permission issues. • 3) Space issues in the backup share/local copy location. 4) SQL Server Agents stopped at Primary/Standy/Monitor. 5) Manual log backup...

Log Shipping Introduction Shipping of Transaction Log files from Primary server database to secondary database to get both databases in continuous Synchronization. It is a continuous process in the form of batches. Log Shipping requires 3 Servers: · Primary...