Posts

Showing posts from August, 2011
How to do any activity like select, delete or truncate for all tables in a databases?
Use databasename
Go sp_tables

Filter the above query output by TABLE_TYPE AS TABLE.
Then copy all table_name column values from the results in SSMS and paste it in the excel sheet columns.
Then join table_name with below statements in another excel column.
Lets take how to truncate all tables in a database.

="TRUNCATE TABLE ” &A1 &" "

Employeesdetails TRUNCATE TABLE  Employeesdetails

Any other method to do the above activity.

How to check existing job is scheduled or not in sql server?

How to check existing job is scheduled or not in sql server?
By running below stored procedure in sql server management studio,we can able to find job schedule details.
Exec msdb..sp_help_jobschedule @job_name='DBA Maintenance plan job’
If it shows row means, job scheduled else not.

Steps for connection related issues in sql server

Please make use of below steps for connection related issues in sql server
Step1:
First check below ping information related to the specific server.
C:\Documents and Settings\username>ping servername
Pinging servername.xx.yyy.com [12.345.67.890] with 32 bytes of data:
Reply from 12.345.67.890: bytes=32 time=96ms TTL=122 Reply from 12.345.67.890: bytes=32 time=95ms TTL=122 Reply from 12.345.67.890: bytes=32 time=95ms TTL=122 Reply from 12.345.67.890: bytes=32 time=96ms TTL=122
Ping statistics for 12.345.67.890:     Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds:     Minimum = 95ms, Maximum = 96ms, Average = 95ms -------------------------------------------------------------------------------- C:\Documents and Settings\ username >ping -a 12.345.67.890
Pinging servername [12.345.67.890] with 32 bytes of data:
Reply from 12.345.67.890: bytes=32 time=97ms TTL=122 Reply from 12.345.67.890: bytes=32 time=95ms TTL=122 Reply from 12.345.67.890: bytes=32 …

Duplicate records finding in all tables in a database.

Duplicate records finding in all tables in a database.

--To find all userdefined tables in the database.
Use databasename GO select*fromsysobjectswhere xtype='u'
--then copy the tables in excelsheet column and calculate using below formula in --another excel column
="select id from "&A1&" group by id where having count(*)>1 "
--Id is the primary key column name,A1 is the table cell name. Finally execute the all tables select query collections from the excel sheet to the database.
Select id fromemployeegroupby id  havingcount(*)>1 Select id fromemployee1 s groupby id  havingcount(*)>1 Select id fromemployee2groupby id  havingcount(*)>1 Select id fromemployee3groupby id  havingcount(*)>1
--if any records come,it seems to be presence of duplicate records…
Is any other method to find?