T-SQL Generate attach and detach database script for all User Databases On a SQL Instance


/* SQL Server: T-SQL Script To Generate Attach and Detach Commands for all User Databases On a SQL Instance */
-- Overview: SQL Scripts that generates commands for your attach and detach stored procedures for all of your user databases
-- Usage: Change the 'SET @DetachOrAttach' parameter to a '0' (attach) or '1' (detach) value to generate your T-SQL commands
-- Environments: SQL Server 2008 / 2012 /2014/2014


USE [master];
GO
DECLARE @database NVARCHAR(200) ,
@cmd NVARCHAR(1000) ,
@detach_cmd NVARCHAR(4000) ,
@attach_cmd NVARCHAR(4000) ,
@file NVARCHAR(1000) ,
@i INT ,
@DetachOrAttach BIT;
SET @DetachOrAttach = 0; -- Change this to '0' for sp_attach_db commands; or '1' for sp_detach_db commands
-- 0 Generates Attach Script
-- 1 Generates Detach Script
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT RTRIM(LTRIM([name]))
FROM sys.databases
WHERE database_id > 4;
-- No system databases
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;
SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
+ 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
-- Change skip checks to false if you want to update statistics before you detach.
SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
+ 'EXEC sp_detach_db @dbname = ''' + @database
+ ''' , @skipchecks = ''true'';' + CHAR(10);
-- Get a list of files for the database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@database)
ORDER BY [file_id];
OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd = @attach_cmd + ' ,@filename'
+ CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
+ CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END
CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;
IF ( @DetachOrAttach = 0 )
BEGIN
-- Output attach script
PRINT @attach_cmd;
END
ELSE -- Output detach script
PRINT @detach_cmd;
FETCH NEXT FROM dbname_cur INTO @database
END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;


SQL server SSIS Notes

Please click on following download button to download SQL server SSIS  Notes 

                                            Download 

How to move system databases from one location to another location in SQL server


Move tempdb:

1) first we have to know the location of tempdb data file and log file location by using below query
use tempdb
go
exec sp_helpfile
go

2) run the below query to save the sql server new location of tempdb data file and log file path

alter database tempdb modify file (name=tempdev, filename='E:\sysdbs\mdf\tempdb.mdf')
go
alter database tempdb modify file (name=templog, filename='E:\sysdbs\ldf\templog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4) start the sql server

5)verify the path for tempdb data file and log file by using below query
exec sp_helpfile
go

6) whenever restart the sql server new tempdb will be created so automatically the new tmpdb will created in new location  goto old location and delete the old tempdb files


move model :

1) first we have to know the location of tempdb data file and log file location by using below query
use model
go
exec sp_helpfile
go

2)run the below query to save the sql server new location of model data file and log file path

alter database model modify file (name=modeldev, filename='E:\sysdbs\mdf\model.mdf')
go
alter database model modify file (name=modellog, filename='E:\sysdbs\ldf\modellog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4)move the  model databse data file and log file from old location to new location

5) start the sql server

6)verify the path for model data file and log file by using below query
exec sp_helpfile
go


move msdb : 

1) first we have to know the location of msdb data file and log file location by using below query
use msdb
go
exec sp_helpfile
go

2)run the below query to save the sql server new location of msdb data file and log file path

alter database msdb modify file (name=msdbdata, filename='E:\sysdbs\mdf\msdbdata.mdf')
go
alter database msdb modify file (name=msdblog, filename='E:\sysdbs\ldf\msdblog.ldf')
go

after execute the above query it shows message like the sql server save your catalog but you must restart the sql server

3) stop the sql server

4)move the  msdb data file and log file from old location to new location

5) start the sql server

6)verify the path for model data file and log file by using below query
exec sp_helpfile
go

move master :

1) copy the startup paramaeter(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

2) stop the sql server

3) move the master data file and log file from old location to new location

4)paste the startup parameter as a new location of master data fil;e and log file

(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)

-dE:\sysdbs\mdf\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lE:\sysdbs\ldf\mastlog.ldf

apply ok

5)it shows one message like system save the ur request but you must restart the sql server to apply these rules

6) start the sql server

7)if run sql server there is no issues if sql server will not  work check the startup parameters(startup parameters are very senstive it doesnt have single extra collan or space etc)

8)check the new path of master database data file and log file by using below query

exec sp_helpfile



T-SQL TRANSACT STRUCTURED QUERY LANGUAGE


T-SQL

TRANSACT STRUCTURED QUERY LANGUAGE

            This is a 4th generated intermediate language between user and SQL Server. Whenever user wants to interact with SQL Server, he has to interact with SQL Server through T-SQL. It includes the following Sub Languages

  1. DDL (Data Definition Language)
  2. DML (Data Manipulation Language)
  3. DCL (Data Control Language)
  4. TCL (Transaction Control Language)


Things to Observe:
  1. While writing the Queries using T-SQL in SQL Server Management Studio we need not to follow any particular case. Because T-SQL is case insensitive language.
  2. After writing the Query, we need to select that query using either mouse or keyboard.
  3. Now Press F5 ( Execute Key).
  4. Then the results are displayed in a separate window called Result window or Result Pane.
  5. Use Ctrl+R to Hide/Show the Result window or Result Pane.
  6. Use F8 for Object Explorer

Note:
  1. SQL SERVER can handle nearly 32767 Databases
  2. Each Database can handle nearly 2 billion Database Objects.
  3. Each Table can handle nearly 1024 columns
  4. Each Table can handle nearly 1 million Rows.

SP in Fundamental level


SP in Fundamental level:


SP_RENAMEDB: Here SP stands for Stored Procedure. This stored procedure is used to change the name of the existing database.

Syntax: SP_RENAMEDB ‘OLD DATABASENAME’, ‘NEW DATABASENAME’

            E.g.: SP_RENAMEDB  ‘NRSTT’, ‘NRSTTS’

The above statement renames (changes the database name) NRSTT to NRSTTS

SP_RENAME: This stored procedure is used for changing the name of the table and for changing the name of the column

i. Syntax to change the name of the table

SP_RENAME ‘OLD TABLENAME’, ‘NEW TABLENAME’

E.g.      SP_RENAME ‘EMP’, ‘EMPLOY’

The above stored procedure changes the name of EMP table to EMPLOY


ii. Syntax to change the name of the column

SP_RENAME ‘TABLE.OLDCOLUMN NAME’, ‘NEW COLUMNNAME’

E.g.      SP_RENAME ‘STUDENT.ADR’, ‘ADDRESS’

The above stored procedure changes the name of ADR column to ADDRESS in STUDENT table.

SP_HELP: This stored procedure is used to display the description of a specific table.

Syntax: SP_HELP TABLENAME

E.g.: SP_HELP EMP

The above stored procedure displays the description of EMP table

SP_DATADASES: This Stored procedure displays the list of databases available in SQL Server.

Syntax: SP_DATABASES



SP_TABLES: This stored procedure displays the list of tables available in the current database.

Syntax: SP_TABLES

SP_HELPDB: This stored procedure is used to display the description of  master and log data file information of a specific database

Syntax: SP_HELPDB Database-Name

Ex: SP_HELPDB SAMPLE

SP_SPACEUSED: This stored procedure is used to find the memory status of the current database

Syntax: SP_SPACEUSED


Collation levels and default collation

Collation levels and default collation:

Collation can be set at 4 levels in SQL Server:
1.       Server
2.       Database
3.       Columns
4.       Expression

But collations are compared either at columns level or in expressions. So I am starting from lower to upper levels. The collations of upper two levels (server and database) are only helpful in providing default collation to columns and expressions.
Expression collation: All literals, variables and parameters and functions without any input parameters, by default get the collation of database.

Column collation: When we create a table we can specify a collation explicitly for all character data type (char, nchar, varchar, nvarchar, text, ntext) columns. If not specified then collation of database would be the default collation of a column. The collation of a column can be changed by using an ALTER TABLE statement similar to the following:

ALTER TABLE TestTab ALTER COLUMN CharCol CHAR(10) COLLATE Greek_CS_AI

Database collation: When we create a new database we can specify collation. If not specify then collation of model database is assigned as default collation. To know the collation of database use below statement:

SELECT DATABASEPROPERTYEX('testDB', 'Collation') SQLCollation

Database collation can be changed using the ALTER DATABASE statement as below.:

ALTER DATABASE myDB COLLATE Greek_CS_AI

When collation of database is changed it does not automatically change the collation of all columns of all tables. But the new collation would be the default collation for new columns created hence forth and for expressions.

Server collation: This is set during SQL Server installation. It’s the default collation for system databases. Because this is the collation of model database so this would be the default collation for all new databases that would be created on this server. The server collation can not be changed. To query the server collation use the below statement:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))

Executing a SQL statement from command prompt:

Executing a SQL statement from command prompt:

Sometimes we need to execute some –T-SQL script from command prompt. This is a common requirement when some t-sql scripts are needed to be scheduled by windows scheduler.

Here I will explain how we can execute T-SQL script from command prompt. Below I created an executable file (.bat) to create a backup of AdventureWorks database so that I can schedule this executable using Windows scheduler.

 Create a text file for example c:\sql.txt and write below backup script in it:

USE master

GO

BACKUP DATABASE AdventureWorks

TO DISK = 'D:\SQL_Backup\AdventureWorks.bak' WITH INIT

Create another batch file for example Backup.bat and write the below statement inside it:

sqlcmd -S ServerName -U yourLogin -P yourPassowrd -i c:\sql.txt

Now we can execute this Backup.bat file from command prompt or can schedule using windows scheduler. Thus we have scheduled a task that is not dependent on SQL server Agent service.

Find who dropped, created or altered the table, SP or any object

Find who dropped, created or altered the table, SP or any object

If any database object is dropped, created or altered accidentally, sometimes you may need information about:

Who dropped, created or altered the object? When culprit not accept the mistake.

When the object was dropped, created or altered? Especially drop time of table is required for point in time recovery from database backups.

These changes are not recorded in SQL Server Error Log but are recorded in default trace. If you have not disabled the default trace and started looking into the issue soon after change occurred you could get this information. The path of trace file is “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\”. The folder MSSQL10.SQLEXPRESS may be different according to your SQL Server instance name. Here you would find 5 profiler trace files named as “log_1”, “Log_2”, etc. The file number is increased by time (in following example I used “log_16.trc”). Find the file that is most recently modified and use the below t-sql query to open that file:

SELECT ObjectName, DatabaseName, StartTime, NTDomainName, HostName, NTUserName, LoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\log_16.trc', default)

WHERE objectname is not null  


You can start a trace as default in two ways:
1. create a stored procedure that starts a trace# and then set that SP as startup as following:

CREATE PROC sp_StartTrace
AS
DBCC TRACEON (1204) 
GO

sp_procoption sp_StartTrace , 'STARTUP', 'ON'

2. Start SQL Server at Run window using NET START command with -T switch as below:
net start SQLSERVER -T 1204