Archive
SQL Server – How to Move Table to Another Schema
Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.
For example, if I create a table using below script it will be created under my default schema which is dbo:
USE [SqlAndMe]
GO
CREATE TABLE Employee
(
ID INT,
Name VARCHAR(20)
)
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee dbo
(1 row(s) affected)
As you can see from the output the table is currently in dbo schema.
Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.
USE [SqlAndMe]
GO
CREATE SCHEMA HumanResources
GO
ALTER SCHEMA HumanResources
TRANSFER dbo.Employee
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee HumanResources
(1 row(s) affected)
As you can see from the output the Employee table is now moved to HumanResources schema.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Finding out Database creation time
Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.
Using sp_helpdb:
You can find the creation time of database using sp_hepldb system procedure.
EXEC sp_helpdb 'SqlAndMe'
GO
This will return database creation time along with other details:
Using catalog view sys.databases:
Database creation time is also available in sys.databases catalog view:
SELECT create_date
FROM sys.databases
WHERE name = 'SqlAndMe'
GO
Result Set:
create_date
2012-11-14 15:19:31.987
(1 row(s) affected)
However, if the database has been detached and reattached to server, the creation time will be changed to attach time. Also, if the database has been restored from a backup after dropping the database creation time will be changed.
Actual creation time of database is stored in the boot page of the database which is retained after restore or detach/attach. This is stored as dbi_crdate.
You can use DBCC PAGE to read creation time of database from boot page:
DBCC TRACEON(3604)
GO
DBCC PAGE('SqlAndMe', 1, 9, 3)
GO
Result Set (Trimmed):
dbi_createVersion = 661 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2011-11-07 21:12:46.357
dbi_filegeneration = 2
dbi_checkptLSN
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Check SQL Agent Job History using T-SQL
To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.
To Open Log File Viewer,
1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:
Alternatively, you can also use below T-SQL code to check Job History:
SELECT [JobName] = JOB.name,
[Step] = HIST.step_id,
[StepName] = HIST.step_name,
[Message] = HIST.message,
[Status] = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END,
[RunDate] = HIST.run_date,
[RunTime] = HIST.run_time,
[Duration] = HIST.run_duration
FROM sysjobs JOB
INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id
/* WHERE JOB.name = 'Job1' */
ORDER BY HIST.run_date, HIST.run_time
Output:
JobName Step StepName Message Status RunDate RunTime Duration
Job1 1 Step1 Executed a.. Succeeded 20120416 173935 10
Job1 0 (Job outcome) The job su.. Succeeded 20120416 173935 10
Job2 1 Step1 Executed a.. Succeeded 20120416 174037 10
Job2 0 (Job outcome) The job su.. Succeeded 20120416 174037 10
Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Purging Database Mail History
SQL Server stores all mails and attachments in msdb database. To avoid unnecessary growth of msdb database you should remove these mail history unless it is required for auditing or other purposes.
To check all mails processed by Database Mail, you can use sysmail_allitems catalog view:
SELECT COUNT(*)
FROM msdb.dbo.sysmail_allitems
Output:
———–
125
There are 3 siblings of this catalog view sysmail_faileditems, sysmail_unsentitems and sysmail_sentitems which shows mails of different status respectively.
If you are frequently sending larger attachments using database mail this can cause msdb to grow rapidly. All attachments stored in msdb database are available in sysmail_attachments.
To delete mail items you can use system stored procedure sysmail_delete_mailitems_sp, it has below syntax:
sysmail_delete_mailitems_sp [@sent_before] [@sent_status]
You can delete mail using either of the parameters, @sent_before deletes all mail that were sent before specified date, and @sent_status deletes all mails with specified status.
For example, to delete all mails which are sent and are older than current month we can use:
EXEC msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = '2012-05-10 00:00:00',
@sent_status = 'sent'
Output:
(100 row(s) affected)
You can query the sysmail_event_log view to check the deletions that has been initiated.
SELECT description
FROM sysmail_event_log
ORDER BY log_date DESC
Output:
description
Mail items deletion is initiated by user “sa”. 100 items deleted.
DatabaseMail process is started
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Four-part object names
Generally it is considered good practice using two-part names for objects. It make the code more readable and avoids confusion if objects with similar names exists in different schemas. Some features requires that two-part naming must be used such as creating a view WITH SCHEMABINDING.
However, you can also use a four-part name to refer objects. Four-part object reference format consists of:
<< Server.Database.Schema.Object >>
Only object name is required to reference an object, (schema name when the object is not in user’s default schema) all else is optional.
For example, all statements below will work in SQL Server:
USE msdb
/* Four-part name */
SELECT Name FROM VGAJJAR2.msdb.dbo.backupset
/* Server name omitted */
SELECT Name FROM msdb.dbo.backupset
/* Server/database name omitted */
SELECT Name FROM dbo.backupset
/* Server/database/schema name omitted,
only default schema name can be omitted */
SELECT Name FROM backupset
/* And this works too… */
SELECT Name FROM …backupset
SELECT Name FROM ..dbo.backupset
SELECT Name FROM .msdb.dbo.backupset
SELECT Name FROM .msdb..backupset
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Creating and using Dump Devices for Backups
A dump device is simply a logical device which redirects to a specified physical device. The main advantage of using a dump device for backups is it reduces the change required to backup code. For example, if you are backing up to a Network location. If you have hard-coded the backup location then the network location change will require a code change as well.
You can create a dump device using SSMS or T-SQL.
To create a dump device using SSMS:
1. Expand "Server Objects" in Object Explorer,
2. Right Click on "Backup Devices" and choose "New Backup Device"
3. Provide a logical Device name and the physical backup file location for dump device
4. Click "OK" to create the dump device.
To create a dump device using T-SQL:
To create a dump device using T-SQL you can use system stored procedure sp_addumpdevice.
USE [master]
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'MyBackups',
@physicalname = N'C:\Database\Backup\BackupDisk.bak'
GO
Result Set:
(1 row(s) affected)
View all dump devices:
You can get the list of existing dump devices on server using sys.backup_devices catalog view:
SELECT *
FROM sys.backup_devices
GO
Result Set:
Name type type_desc physical_name
———– —– ———– ——————————–
MyBackups 2 DISK C:\Database\Backup\BackupDisk.bak
(1 row(s) affected)
Using the dump device for BACKUP:
Once the dump device is created you can use it to store backups, The dump device will be listed in "Select Backup Destination" dialog box:
You can use it in T-SQL as below:
/*
BACKUP DATABASE [SqlAndMe]
TO DISK = N'C:\Database\Backup\SqlAndMeBackup.bak'
WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
*/
BACKUP DATABASE [SqlAndMe]
TO [MyBackups]
WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Result Set:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 44680 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.
100 percent processed.
Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 2.
BACKUP DATABASE successfully processed 44681 pages in 23.542 seconds (14.827 MB/sec).
Changing Physical File location for dump devices:
To change the physical file location for dump device, you need to drop and recreate the dump device. If you need to move the current physical file to a new location, it can be done using Windows Explorer or any other File Manager. The physical file is not locked unless a BACKUP/RESTORE is in progress.
USE [master]
GO
EXEC master.dbo.sp_dropdevice
@logicalname = N'MyBackups'
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'MyBackups',
@physicalname = N'C:\NewLocation\BackupDisk.bak'
GO
SELECT *
FROM sys.backup_devices
GO
Result Set:
Device dropped.
Name type type_desc physical_name
———— —— ———— ———————–
MyBackups 2 DISK C:\NewLocation\BackupDisk.bak
(1 row(s) affected)
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using Catalog Views – sys.server_role_members & sys.database_role_members
sys.server_role_members contains the membership of each server principal with server roles. It returns a pair of role_principal_id and member_principal_id for each membership as below:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT role_principal_id, member_principal_id
FROM sys.server_role_members
Result Set:
role_principal_id member_principal_id
—————– ——————-
3 1
3 259
3 260
3 261
3 262
9 279
(6 row(s) affected)
sys.server_role_members can be joined with sys.server_principals to retrieve role/member name:
SELECT A.name AS 'Role', B.name AS 'Login'
FROM sys.server_role_members C
INNER JOIN sys.server_principals A ON A.principal_id = C.role_principal_id
INNER JOIN sys.server_principals B ON B.principal_id = C.member_principal_id
Result Set:
Role Login
——————- ———————-
Sysadmin sa
Sysadmin NT AUTHORITY\SYSTEM
Sysadmin NT SERVICE\MSSQLSERVER
Sysadmin ***********************
Sysadmin NT SERVICE\SQLSERVERAGENT
Dbcreator Vishal
(6 row(s) affected)
sys.server_role_members contains information for server level roles, for database level roles sys.database_role_members can be used. It returns a pair of role_principal_id and member_principal_id for each membership:
SELECT role_principal_id, member_principal_id
FROM sys.database_role_members
Result Set:
role_principal_id member_principal_id
—————– ——————-
16384 1
16384 5
16384 6
16385 5
16385 6
16386 5
16387 5
16387 6
16389 5
16390 5
16390 6
16391 5
16391 6
(13 row(s) affected)
It can be joined with sys.database_principals to retrieve role/member name as below:
SELECT A.name AS 'Role', B.name AS 'User'
FROM sys.database_role_members C
INNER JOIN sys.database_principals A ON A.principal_id = C.role_principal_id
INNER JOIN sys.database_principals B ON B.principal_id = C.member_principal_id
Result Set:
Role User
——————– ——————–
db_owner dbo
db_owner sqladmin
db_accessadmin sqladmin
db_securityadmin sqladmin
db_ddladmin sqladmin
db_backupoperator sqladmin
db_datareader sqladmin
db_datawriter sqladmin
db_owner NewUser
db_accessadmin NewUser
db_ddladmin NewUser
db_datareader NewUser
db_datawriter NewUser
(13 row(s) affected)
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using Catalog Views – sys.database_principals
While sys.server_principals contains all logins/roles created on the server. sys.database_principals contains all users/roles in the current database. Some of the columns returned by sys.database_principals are as below:
1. Name – name of the principal – user/role name,
2. Principal_id – id of the principal,
3. Type – type of the principal – S = SQL user (mapped to a SQL Login), U = Windows user (mapped to a Windows Login), G = Windows group (mapped to a Windows Group Login), A = Application role, R = Database role, C = Certificate mapped user, K = Asymmetric key mapped user,
4. Type_desc – Textual description of 'Type',
5. Default_schema_name – Default schema name to be used when schema name is not specified,
6. Create/Modify_date – Creation/Modification date,
7. Sid – Security identifier, applicable only to SQL User, Windows User and Windows Group,
8. Is_fixed_role – 1 if it’s a fixed database role
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT name, principal_id, type, default_schema_name,
create_date, modify_date, sid, is_fixed_role
FROM sys.database_principals
Partial Result Set:
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using Catalog Views – sys.server_principals
sys.server_principals contains all logins created on the server. It also contains server roles. Some of the columns returned by sys.server_principals are as below:
1. Name – Login Name,
2. Principal_id – Id number of the principal object,
3. Sid – Security Identifier of the login. In case of Windows mapped logins, this will be same as SID in Active Directory,
4. Type – Type of login – S = SQL Login, U = Windows Login, G = Windows Security Group Login, R = Server-role, C = Certificate mapped Login, K = Asymmetric Key mapped login,
5. Type_desc – Textual description of 'Type',
6. Is_disabled – 1 = Disabled,
7. Create/Modify_date – Creation / Modification date for login,
8. Default_database_name – Default database context for the login.
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT name, principal_id, sid, type, type_desc, is_disabled,
create_date, modify_date, default_database_name
FROM sys.server_principals
Partial Result Set:
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using Catalog Views – sys.identity_columns
sys.identity_columns contains a list of all identity columns in the database, some of the columns returned by sys.identity_columns are as below:
1. Object_id – object id of the parent table,
2. Name – column name for the identity column,
3. Column_id – id of the columns in the table,
4. Is_identity – 1,
5. Seed_value – Initial seed valued for the identity column,
6. Increment_value – Increment value specified for the column,
7. Last_value – last identity value that was used for the table,
8. Is_not_for_replication – 1, if the column was created with NOT FOR REPLICATION,
It can be used as:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT object_id, name, column_id, is_identity,
seed_value, increment_value, last_value, is_not_for_replication
FROM sys.identity_columns
Partial Result Set:
To retrieve table name, it can be joined with sys.tables or OBJECT_NAME() function can be used as below:
SELECT OBJECT_NAME(object_id) AS 'Table Name', name, column_id, is_identity,
seed_value, increment_value, last_value, is_not_for_replication
FROM sys.identity_columns
Partial Result Set:
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
