Dushyanth Jyothi

SQL Sever Error 3154: The backup set holds a backup of a database other than the existing database.

Tested only on MS SERVER 2008

 T-SQL | 
 copy code |
?
 
01
USE master
02
GO
03
 
04
-- Database to restore : DJ_ADMIN
05
-- Resore With: DJ_ADMIN_Latest.bak
06
-- Backup existing database's to : DJ_ADMIN_Old.bak;DJ_ADMIN_Old_Log.bak
07
-- Database to restore : DJ_ADMIN
08
 
09
ALTER DATABASE DJ_ADMIN
10
SET SINGLE_USER WITH
11
ROLLBACK IMMEDIATE
12
RESTORE DATABASE DJ_ADMIN
13
FROM DISK = 'C:\Backups\DJ_ADMIN_Latest.bak'
14
WITH MOVE 'DJ_ADMIN' TO 'C:\Backups\DJ_ADMIN_Old.mdf',
15
MOVE 'DJ_ADMIN_Log' TO 'C:\Backups\DJ_ADMIN_Old_Log.ldf',
16
REPLACE

Resetting/Changing the identity[auto-increment number] column value of a table

DBCC CHECKIDENT checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column

Tested only on MS SERVER 2008

Table to alter:

 T-SQL | 
 copy code |
?
 
1
CREATE TABLE ADMIN.DocumentType_List
2
(
3
	DocumentType_ID INT IDENTITY(0,1) NOT NULL,
4
	DocumentType NVARCHAR(250) NOT NULL,
5
	Description_ NVARCHAR(250) NOT NULL
6
CONSTRAINT PK_DocumentType PRIMARY KEY (DocumentType_ID)
7
)
8

In the above code 'DocumentType_ID' column has declared as Identity, having auto increment value by 1 starting with 0. This means the first row inserted will have 'DocumentType_ID' value of 1. then 2,3,so on..

In future if we want to reset this value, for what so ever reason, we can use T-SQL:

 T-SQL | 
 copy code |
?
 
1
2
DBCC CHECKIDENT('Admin.DocumentType_List', RESEED, 0)

This code will reset table Identity back to 0.

Further Reading/Details:http://msdn.microsoft.com/en-us/library/ms176057(SQL.90).aspx

T-SQL code [Stored Procedure] to loop through all tables and all columns in a database

Applies to MS SQL SERVER 2008

Change [DATABASE_NAME],[SCHEMA_NAME],[USER_NAME] accordingly


Stored Procedure:

 T-SQL | 
 copy code |
?
 
01
USE [DATABASE_NAME]
02
GO
03
SET ANSI_NULLS ON
04
GO
05
SET QUOTED_IDENTIFIER ON
06
GO
07
CREATE PROC [SCHEMA_NAME].[loop_Through_Tables_Columns]
08
AS
09
BEGIN
10
 
11
	SET NOCOUNT ON
12
	DECLARE @TableName NVARCHAR(256)
13
	DECLARE @ColumnName NVARCHAR(128)
14
	SET  @TableName = ''
15
 
16
	WHILE @TableName IS NOT NULL
17
	BEGIN
18
		SET @ColumnName = ''
19
		SET @TableName = 
20
			(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES
21
				WHERE TABLE_TYPE = 'BASE TABLE' /**You can remove this condition to go thourgh with both views and tables**/
22
					AND	 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
23
					AND	OBJECTPROPERTY(
24
						OBJECT_ID(
25
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
26
							 ), 'IsMSShipped'
27
						       ) = 0
28
			)
29
 
30
		PRINT 'TableName:  '+ CAST(@TableName AS CHAR)
31
 
32
			WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
33
			BEGIN
34
				SET @ColumnName =
35
				(
36
					SELECT MIN(QUOTENAME(COLUMN_NAME))
37
					FROM INFORMATION_SCHEMA.COLUMNS
38
					WHERE TABLE_SCHEMA	= PARSENAME(@TableName, 2)
39
						AND	TABLE_NAME	= PARSENAME(@TableName, 1)
40
						/**You you want to go thourgh with certain type of data types**/
41
						AND	DATA_TYPE IN('char','varchar','nchar','nvarchar','text','ntext','image','xml','varbinary')
42
						AND	QUOTENAME(COLUMN_NAME) > @ColumnName
43
				)
44
				PRINT 'ColumnName:  '+ CAST(@ColumnName AS CHAR)				
45
			END
46
	END
47
 
48
END
49
GRANT EXECUTE ON [SCHEMA_NAME].[loop_Through_Tables_Columns] TO USER_NAME 

Execute Stored Procedure:

 T-SQL | 
 copy code |
?
 
1
EXEC [SCHEMA_NAME].[loop_Through_Tables_Columns]

Beginning SQL Server 2005 XML Programming by Srinivas Sampath [21 February 2006]
Source:http://www.simple-talk.com

http://www.simple-talk.com/sql/sql-server-2005/beginning-sql-server-2005-xml-programming/

Search all columns of all tables in a database

How to search all columns of all tables in a database for a keyword?

Credits to: Vyas

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

Here is the complete stored procedure code:

–To search all columns of all tables in Pubs database for the keyword “Computer”

 T-SQL | 
 copy code |
?
 
01
CREATE PROC SearchAllTables
02
(
03
 @SearchStr NVARCHAR(100)
04
)
05
AS
06
BEGIN
07
<span style="font-family: verdana; color: gray; font-size: xx-small;">
08
 -- Copyright � 2002 Narayana Vyas Kondreddi. All rights reserved.
09
 -- Purpose: To search all columns of all tables for a given search string
10
 -- Written by: Narayana Vyas Kondreddi
11
 -- Site: http://vyaskn.tripod.com
12
 -- Tested on: SQL Server 7.0 and SQL Server 2000
13
 -- Date modified: 28th July 2002 22:50 GMT
14
</span>
15
 
16
 CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))
17
 
18
 SET NOCOUNT ON
19
 
20
 DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110)
21
 SET  @TableName = ''
22
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
23
 
24
 WHILE @TableName IS NOT NULL
25
 BEGIN
26
 SET @ColumnName = ''
27
 SET @TableName =
28
 (
29
 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
30
 FROM 	INFORMATION_SCHEMA.TABLES
31
 WHERE 		TABLE_TYPE = 'BASE TABLE'
32
 AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
33
 AND	OBJECTPROPERTY(
34
 OBJECT_ID(
35
 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
36
 ), 'IsMSShipped'
37
        ) = 0
38
 )
39
 
40
 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
41
 BEGIN
42
 SET @ColumnName =
43
 (
44
 SELECT MIN(QUOTENAME(COLUMN_NAME))
45
 FROM 	INFORMATION_SCHEMA.COLUMNS
46
 WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
47
 AND	TABLE_NAME	= PARSENAME(@TableName, 1)
48
 AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
49
 AND	QUOTENAME(COLUMN_NAME) > @ColumnName
50
 )
51
 
52
 IF @ColumnName IS NOT NULL
53
 BEGIN
54
 INSERT INTO #Results
55
 EXEC
56
 (
57
 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
58
 FROM ' + @TableName + ' (NOLOCK) ' +
59
 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
60
 )
61
 END
62
 END
63
 END
64
 
65
 SELECT ColumnName, ColumnValue FROM #Results
66
END
67

 T-SQL | 
 copy code |
?
 
1
EXEC SearchAllTables 'Computer'
2
GO
3