How to Delete all records from all tables from Database

Following is the SQL query used to delete all records from all tables from Database.
This is useful during development scenarios where you need to get rid of all the dummy records and insert a fresh data.

SQL Query: Appropriate comments are provided to explain its operation

/* The Following set of queries is used to delete all records from all tables from your database */

/* Disables Referential integrity */

/*---------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

/* Delete records from tables */

/*----------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?

'

GO

/* Enables Referential integrity */

/*-------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

/*Query ot be used if you would like to reseed all table*/

/*------------------------------------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)

'

GO

Source : http://mrsupport.blogspot.in/

Read More

Search for a Text in whole Database SQL

Search for a Text in whole Database SQL

Hi,

There was a requirement to search for a text in the whole SQL database. found this Stored procedure very useful. though it is worth sharing...

Resource: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

-- SearchAllTables ''

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

END

source: http://mrsupport.blogspot.com/

Read More

Grant of Transport Allowance to the Running Staff only taking into account pay plus 30% of pay

GOVERNMENT OF INDIA
MINISTRY OF RAILWAYS
(RAILWAY BOARD)

No. E(P&A) II-2011/RS-51

RBE No.5/2012
New Delhi, dated 17.04.2012

The General Managers,
All Indian Railways & Production Units etc.,
(As per mailing list No.I & II)

Sub: Grant of Transport Allowance to the Running Staff only taking into account pay plus 30% of pay.

One of the Zonal Railways had sought clarification whether 30% pay element is to be reckoned for the purpose of payment of Transport Allowance to Running Staff or otherwise.

2. The issue has already been considered and also discussed with the representatives of the Staff Side after the implementation of the Fifth CPC recommendations but was not agreed to. Therefore, it is clarified that 30% pay elements, is not to be reckoned for the  purpose of payment of Transport Allowance to Running Staff.

3. This issues with the concurrence of the Finance Directorate of the Ministry of Railways.

sd/-

(Salim Md.Ahmad)

Deputy Director /E(P&A)II,
Railway Board

Source: NFIR

Read More

Procedure of sending SO_TO_HO files at Head Post Office

Procedure of sending SO_TO_HO files at Head Post Office

 1. First ensure that following points.A] In Sanchay Post à Superviosr à PO name and Address --> New Form --> See the Your office and Head Post Office details is feed. Secondly in below see your office the running SB all categories are add in boxes and saved.

B] Installed the latest DBAnlayzer (20.03.2012) and entered the Server name --> Select the radio button of DATA TRANFSER --> Select the radio button of SO_TO_HO option and below select the one by one in your office running all SB categories. ( After selection the said SB categories button are disabled. )

C] Check the SO_TO_HO folder in My Computer à C:\ drive .

2. Now you ensure that you have already received the your office USER ID and PASSWORD for uploading the SO_TO_HO data

3. After the day end of Sanchay Post and Time Transaction Report check the SO_TO_HO folder and noticed that in your office running all SB categories files are created in this folder.

4. Start the Internet and type following website is
www.tamilnadupost.nic.in/sanchaypost/

5. Entered the USER ID and PASSWORD ( IN CAPITAL LETTER ) and click on LOGIN button

6. Now you see the file uploading screen and select one by one file from C:\SO_TO_HO folder through BROWSE button.

7. After uploading the all files click on UPLOAD button.

8. After successfully uploading the all created file LOGOUT the website.

**FOR ACCURACY AND CORRECTNESS IN SANCHAY POST DATA DONE THE PROPER TRANSACTIONS AND AVOID MANUALLY CORRECTION ON -- LIST OF TRANSACTION**.

Read More

Three postal employees, accomplice caught stealing parcels

New Delhi, April 22,2012(IANS): Four men, including three postal department employees, were arrested for stealing parcels from a post office van in the capital’s cantonment area, police said Sunday.

According to police, out of the accused, Jai Bhagwan, 47, Shiv Shankar, 55, and Janardan Majhi, 47, were employees of the post and telegraph department. The fourth was identified as Vinod Kumar, 48, an accomplice.

“They (accused) had parked a postal department parcel van and were stealing the contents of parcels when an officer from the department saw them and informed the police,” a police official told IANS.

Acting upon the information, the police team apprehended the culprits red-handed.

“They had all the materials required for opening and resealing the parcels so that the theft could not be detected immediately,” the officer added.

The accused were presented in court Saturday and have been sent to Tihar jail for 14 days’ judicial custody.

source:tkbsen.com

Read More

Central Government releases fund for Delhi to award Post Matric Scholarship

Press Information Bureau
Government of India
Ministry of Social Justice & Empowerment

23-April-2012 17:28 IST

Central Government releases fund for Delhi to award Post Matric Scholarship

Ministry of Social Justice & Empowerment has released an amount of Rs.9,79,40,000 /-(Rupees- Nine crore Seventy nine lakh forty thousand only) to the NCT of Delhi, towards central assistance for the current financial year 2011-2012,under the centrally sponsored scheme of Post-Matric Scholarships to student belonging to Scheduled Castes.

The Post Matric Scholarship for SC Students Scheme is a single largest intervention by Government of India for educational development of scheduled caste students. It is a flagship Scheme of the Ministry, in operation since 1944, which provides financial assistance to Scheduled Caste students studying at post matriculation level in the country. There is no upper limit on the number of scholarships and all the SC students fulfilling the eligibility criteria are covered under the Scheme. About 48 lakh students are anticipated to be covered during 2011-12.During X FY Plan, an amount of Rs. 1826.91 Crore was released to cover 113.82 lakh beneficiaries. It has increased sharply in XI FY Plan and Rs.6780.52 crore has been released so far to cover 196.16 lakh beneficiaries.

In order to streamline the delivery system of the Scheme and fulfilling the objective of improving enrollment and retention of SC students all the State Governments and Administrators of UTs have been advised by Ministry to ensure timely and regular disbursement of scholarship to the beneficiary students, payment through bank/post-office accounts, start online processing of scholarship and designate Grievance Redressal Officers (GROs) at the State and District levels to redress students’ scholarship-related grievances. The Scheme is implemented through State Governments and Union Territory Administrations which receive 100% Central assistance from the Government of India for the total expenditure under the scheme, over and above their respective Committed Liability. The North Eastern states have been exempted from the burden of committed liability.

The Scheme has been revised by the Government on 31-12-2010 w.e.f. 1.7.2010 and the revision includes increase in

(i) Parental income ceiling from Rs. 1.00 lakh p.a. to Rs. 2.00 lakh p.a.;

(ii) regrouping of courses; and

(iii) rates of maintenance and other allowances.

Rates of maintenance and other allowances have been increased by 60% with respect to pre-revised rates. Presently, 23 State Governments have already implemented revised Scheme. Others are under process to implement the revised Scheme.

Read More