:::: MENU ::::

Blog

  • Aug 16 / 2013
  • 0
dbDigger, Monitoring and Analysis, Replication, T-SQL Scripts

Get list of replicated objects in a database

If replication is implemented then it is important to have a list of objects that have been replicated. Replicated objects are known as article in terms of replication setup. So articles may have slightly different concerns in terms of performance, drop or truncate command. Use the following simple script to get the list of replicated objects in a database.

 USE [Ur DB Name Here]  
 GO  
 SELECT NAME AS [Object Name]  
   ,type_desc AS [Object Type]  
   ,is_published AS [Is Data and Schema Published]  
   ,is_schema_published AS [Is Only Schema Published]  
 FROM sys.objects  
 WHERE is_ms_shipped = 0  
 AND (is_published = 1 OR is_schema_published = 1)  

Get list of replicated objects in a database
[Is Data is Schema Published] is for tables. While [Is Only Schema Published] is for other objects like USPs, UDF and Views.

  • Apr 11 / 2013
  • 0
dbDigger, ETL, T-SQL Interview Questions, T-SQL Tips and Tricks

Using TOP in DELETE statement for T-SQL

Suppose we have a WHILE loop for deleting specified amount of data batches in each loop. Simple solution is to use TOP operator in loop for specifying the number of rows to be deleted in each batch. But it generates error while trying to use TOP operator in delete operation. Let us have a look at the problem.

-- Create a table with only one column in it  
 CREATE TABLE DemoTable (id SMALLINT identity(1, 1))  
 GO  
 -- Insert 100 values in identity column  
 INSERT INTO DemoTable DEFAULT VALUES   
 GO 100  
 -- Verify the values   
 SELECT * FROM DemoTable  
 GO  
 -- Try to delete statement for top 10 rows  
 DELETE TOP 10 FROM DemoTable  

but following error message is generated

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ’10’.

It looks that TOP operator is not working in the DELETE statement. So can we use the TOP operator in DELETE operations or not?

Answer is YES, we may use TOP operator in DELETE operations but with little modification. Correct statement is as under with brackets around the number.

 -- Using TOP in delete statement  
 DELETE TOP (10) FROM DemoTable  

So we have deleted the 10 records from demo table by using TOP operator in DELETE statement. Similarly instead of constant number any variable may be used in brackets with TOP.

 -- Drop DemoTable  
 DROP TABLE DemoTable  

However it is notable that according to BOL
“TOP cannot be used together with UPDATE and DELETE statements on partitioned views.”.

  • Feb 14 / 2013
  • 1
dbDigger, T-SQL Tips and Tricks

Count characters in MS Word 2007 document

Working with my article for MSSQLTips.com i was required to put roughly 4000 characters in code of a stored procedure. After pasting the text in a word document i looked for any information about characters. However word 2007 was displaying number of words at bottom bar.

Count characters in MS Word 2007 document

So in this situation the easy and handy method to get count for characters, words, paragraphs, pages and lines just click on the words count being displayed at bottom bar of document. It would provide all required information.

get count for characters, words, paragraphs, pages and lines in word document

I beleive there would be other methods to access this information but i think this one is most handy and applicable to other versions of MS Word.

  • Dec 20 / 2012
  • 0
Cursors, dbDigger, T-SQL Scripts, User Defined Stored Procedures

Drop specific tables through a stored procedure by using Cursor

Suppose we have to create a procedure which would drop all the tables having a given naming convention. Procedure would utilize a cursor and dynamic SQL. Table names would be pulled from SYS.Objects catalog. Here is the commented procedure for demo

/*   
 Demo would drop the required tables  
 by using a cursor in following steps  
 1. Create tables for drop demo  
 2. Verify the created tables  
 3. Create USP  
 4. Create cursor in USP to pull and execute the drop commands  
 5. Execute the procedure  
 6. Verify that tables do not exist now  
 */  
 -- Create Tables  
 create table rep1 (id smallint)  
 GO  
 create table rep2 (id smallint)  
 GO  
 create table rep3 (id smallint)  
 GO  
 create table rep4 (id smallint)  
 GO  
 -- verify the created tables   
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

 -------------------------------------------------------------  
 -- Create USP and a cursor in USP  
 If exists (select name from sys.objects   
 where type = 'P' and name = 'USP_DropRepTables')  
 DROP PROCEDURE USP_DropRepTables  
 GO  
 CREATE PROCEDURE USP_DropRepTables  
 AS  
 -- Declare variable for SQL command  
 DECLARE @dropQuery NVARCHAR(400)  
 -- Declare and populate cursor with required tables  
 DECLARE DropLoopCursor CURSOR FOR  
 SELECT 'DROP Table '+name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 -- Open cursor  
 OPEN DropLoopCursor  
 -- Fetch record from cursor  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 -- Configure while loop in cursor  
 WHILE (@@FETCH_STATUS <> -1)  
 BEGIN  
 -- Execute Dynamical SQL  
 execute (@dropQuery)  
 -- Fetch next recod  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 END  
 -- Close and deallocate the cursor  
 CLOSE DropLoopCursor  
 DEALLOCATE DropLoopCursor  
 ------------------------------------------------------------  
 -- End the procedure  
 GO  

 -- Execute the procedure  
 EXECUTE USP_DropRepTables  
 GO  

 -- Verify that tables do not exist now  
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

You may copy and paste the whole code in SSMS query pane for a better view of comments. Also it is required to take care that given naming convention should not be used by any such table that is not required to drop.

  • Dec 13 / 2012
  • 0
dbDigger, Disaster Recovery, Maintenance plans, SQL Server Agent scheduled Jobs, SQL Server Error messages, Transaction log files

Log backups fail after changing the SIMPLE recovery model

Recently a scenario was shared with me where maintenance plan was failed to create the log backups. Actually recovery model of DB was set to SIMPLE to prevent the log file population for some log intensive bulk operations. After completing the operations recovery model was put back to FULL. Every thing was OK till this point but maintenance plan job began to failed later for creating log backups of that database with following error

BACKUP LOG cannot be performed because there is no current database backup.

The reason for error is that after changing the recovery model of DB from SIMPLE to BULK LOGGED or FULL, we have to create a full or differential backup before going for log backup.
Solution to avoid such error is simple that we have to go in following sequence

  • Change recovery model to simple
  • Complete the required operations
  • Change back to FULL or BULK LOGGED recovery model
  • Create FULL or DIFFERENTIAL backup
  • Successfully create log backups
  • Here is a quick demo to simulate the above steps

    USE [master]  
     GO  
     -- Set the DB to simple recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY SIMPLE  
     GO   
     -- Set the DB to full recovery model  
     ALTER DATABASE [DBDigger]   
     SET RECOVERY FULL  
     GO   
     -- Try to Backup the log, it would fail with error  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
     -- Now Create full or differential backup of database  
     Backup DATABASE DBDIgger   
     to Disk = 'C:DBD-FullBackup.bak'   
     GO  
     -- Try to Backup the log, it would be OK  
     Backup LOG DBDIgger   
     to Disk = 'C:DBD-LogBackup.Log'  
     GO  
    
    • Dec 03 / 2012
    • 0
    dbDigger, Monitoring and Analysis, SQL Server Agent scheduled Jobs, T-SQL Scripts

    Get list of scheduled jobs and associated steps

    I am required to generate a list of SQL Server scheduled jobs along with there associated steps. It would be compared to same list from another server where the jobs just have been transferred. Following code would provide me job names along with their steps

     USE MSDB  
     GO  
     SELECT v.name AS JobName, step_name   
     FROM sysjobs_view v   
     inner join sysjobsteps t ON v.job_id = t.job_id  
     ORDER BY v.name, step_name  
     GO   
    

    We may add further properties of job or step from SYSJOBS_VIEW and SYSJOBSTEPS respectively.

    Consult us to explore the Databases. Contact us