Wednesday, August 17, 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 time=95ms TTL=122
Reply from 12.345.67.890: bytes=32 time=95ms 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 = 97ms, Average = 95ms

---------------------------------------------------------------------------------

If I ping server name, it show server name with fully qualified name. If I reverse ping with ip address, it show only server name not with fully qualified name.
We couldn't able to find fully qualified name along with server name.

 Step2:

Resolution:

Ask DNS support team to make changes in DNS entry to resolve above reverse ping issue.

After that change, we can able to connect SQL server with no issues.


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 * from sysobjects where 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 from employee group by id  having count(*)>1
Select id from employee1 s group by id  having count(*)>1
Select id from employee2 group by id  having count(*)>1
Select id from employee3 group by id  having count(*)>1

--if any records come,it seems to be presence of duplicate records…

Is any other method to find?

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...