SQL Server Code Name

SQL Server Code Name

Yesterday, I came across a very interesting question on one of the SQL Servers Online Forums.

Question – What is the code name of SQL Server 2005?

Well I knew that the Microsoft gives code names to it products when they are in developmental stages. But I never came across the code name for SQL Serves!

So I did some research on Net to find code name of all the SQL Server Release till date. They are listed below.

SQL Server Release Project Code Name
SQL Server 6.0 SQL95
SQL Server Enterprise Manager Starfighter
SQL Server 6.5 Hydra
SQL Server 7.0 Sphinx
SQL Server 7.0 Plato
SQL Server 2000 (32-bit) Shiloh
SQL Server 2000 (64-bit) Liberty
SQL Server Reporting Services Rosetta
SQL Server 2005 Yukon
SQL Server 2005 Mobile Edition Laguna
SQL Server 2008 Katmai / Akadia
SQL Report Designer 2.0 Blue
SQL Server 2008R2 Killimanjaro

 

Good Luck!

SQL Server – DBCC Commands

DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.

I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.

The DBCC Commands broadly falls into four categories:

  • Maintenance
  • Informational
  • Validation
  • Miscellaneous

Maintenance Commands

Performs maintenance tasks on a database, index, or filegroup.

1. CLEANTABLE – Reclaims space from the dropped variable-length columns in tables or index views.

DBCC CLEANTABLE (‘AdventureWorks’,’Person.Contact’,0)

2. DBREINDEX – Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead)

USE AdventureWorks

DBCC DBREINDEX (‘Person.Contact’,’PK_Contact_ContactID’,80)

3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.

DBCC DROPCLEANBUFFERS

4. FREEPROCCACHE – Removes all elements from the procedure cache

DBCC FREEPROCCACHE

5. INDEXDEFRAG – Defragments indexes of the specified table or view.

DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)

6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database

DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)

7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

USE AdventureWorks;

– Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks_Log, 1)

8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.

DBCC UPDATEUSAGE (AdventureWorks)

Informational Commands

Performs tasks that gather and display various types of information.

1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.

DBCC CONCURRENCYVIOLATION

2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.

DBCC INPUTBUFFER (52)

3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

DBCC OPENTRAN;

4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

DBCC OUTPUTBUFFER (52)

5. PROCCACHE – Displays information in a table format about the procedure cache.

DBCC PROCCACHE

6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table

USE AdventureWorks

DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid)

7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.

USE AdventureWorks

DBCC SHOWCONTIG (‘HumanResources.Employee’);

8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

DBCC SQLPERF(LOGSPACE)

9. TRACESTATUS – Displays the status of trace flags.

DBCC TRACESTATUS(-1)

10. USEROPTIONS – Returns the SET options active (set) for the current connection.

DBCC USEROPTIONS

Validation Commands

Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC (AdventureWorks)

2. CHECKCATALOG – Checks for catalog consistency within the specified database.

DBCC CHECKCATALOG (AdventureWorks)

3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.

DBCC CHECKDB (AdventureWorks)

5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

USE AdventureWorks

DBCC CHECKFILEGROUP

6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.

USE AdventureWorks;

DBCC CHECKIDENT (‘HumanResources.Employee’)

7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

USE AdventureWorks;

DBCC CHECKTABLE (‘HumanResources.Employee’)

Miscellaneous Commands

Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.

DBCC xp_sample (FREE)

2. TRACEOFF – Disables the specified trace flags.

DBCC TRACEOFF (3205)

3. HELP – Returns syntax information for the specified DBCC command.

– List all the DBCC commands

DBCC HELP (‘?’)

– Show the Syntax for a given DBCC commnad

DBCC HELP (‘checkcatalog’)

4. TRACEON – Enables the specified trace flags.

DBCC TRACEON (3205)

Good Luck!

SQL Server – System Stored Procedure Script

System Stored Procedure Script

Have you seen script of System Stored Procedure of SQL Server? Well if you are curious creature like me, then why not see it.

To do that, we need to use another System Stored Procedure sp_helptext. It will display the script behind the specified System Stored Procedure.

Syntax:

EXEC sp_helptext ‘System Stored Procedure’

Example:

- – To see the script of sp_helplogins

EXEC sp_helptext ‘master..sp_helplogins’

Go ahead and try it with other System Stored Procedures.

Did you see how Microsoft codes their Stored Procedures? If you think “Even I can do this stuff“, feel luck and please forward your resume to Microsoft Corporation! :-)

Good Luck!

Bhavesh Patel is now 1000 times Stronger

Bhavesh Patel is now 1000 times Stronger

I don’t know what are you are thinking after reading the title of this post? Did you see the readers count on the right side? Yes, it shows 1000+ readers for this blog.

I started this blog as an experiment few months back when I had just started my career as DBA professional. I wanted to share the knowledge I acquire in this profession with the new DBA’s or “want to be DBA’s”.

It has been a great journey from 0 to 1 then 10 and 100 and now 1000+. I hope posts on this blog are of any use to the readers. I dreamed of positive response from Netizens, but never expected to be this good in such a short period of time.

Till now, most of the posts were related to TSQL commands and some basic programming. Now I shall move on next level of DBA, that is SQL Server Management.  And shall post more advance topics related to DBA in the future.

I’ve always believed in sharing, since I think it makes us all stronger.

Thanks,

Bhavesh Patel

Junior DBA

SQL Server – SERVERPROPERTY Function

SERVERPROPERTY Function

How does one get list all Server Properties? Well, we can use a very helpful system function SERVERPROPERTY. It will return information about the server instance for a specified property.

Syntax:

SERVERPROPERTY (propertyname)

I have kept the following script in my archives, you may do the same.

– Returns all the property information about the server instance

 

SELECT ‘BuildClrVersion’ AS ‘Server Property’, SERVERPROPERTY(‘BuildClrVersion’) AS ‘Value’

UNION ALL

SELECT ‘Collation’, SERVERPROPERTY(‘Collation’)

UNION ALL

SELECT ‘CollationID’, SERVERPROPERTY(‘CollationID’)

UNION ALL

SELECT ‘ComparisonStyle’, SERVERPROPERTY(‘ComparisonStyle’)

UNION ALL

SELECT ‘ComputerNamePhysicalNetBIOS’, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)

UNION ALL

SELECT ‘Edition’, SERVERPROPERTY(‘Edition’)

UNION ALL

SELECT ‘EditionID’, SERVERPROPERTY(‘EditionID’)

UNION ALL

SELECT ‘EngineEdition’, SERVERPROPERTY(‘EngineEdition’)

UNION ALL

SELECT ‘InstanceName’, SERVERPROPERTY(‘InstanceName’)

UNION ALL

SELECT ‘IsClustered’, SERVERPROPERTY(‘IsClustered’)

UNION ALL

SELECT ‘IsFullTextInstalled’, SERVERPROPERTY(‘IsFullTextInstalled’)

UNION ALL

SELECT ‘IsIntegratedSecurityOnly’, SERVERPROPERTY(‘IsIntegratedSecurityOnly’)

UNION ALL

SELECT ‘IsSingleUser’, SERVERPROPERTY(‘IsSingleUser’)

UNION ALL

SELECT ‘LCID’, SERVERPROPERTY(‘LCID’)

UNION ALL

SELECT ‘LicenseType’, SERVERPROPERTY(‘LicenseType’)

UNION ALL

SELECT ‘MachineName’, SERVERPROPERTY(‘MachineName’)

UNION ALL

SELECT ‘NumLicenses’, SERVERPROPERTY(‘NumLicenses’)

UNION ALL

SELECT ‘ProcessID’, SERVERPROPERTY(‘ProcessID’)

UNION ALL

SELECT ‘ProductVersion’, SERVERPROPERTY(‘ProductVersion’)

UNION ALL

SELECT ‘ProductLevel’, SERVERPROPERTY(‘ProductLevel’)

UNION ALL

SELECT ‘ResourceLastUpdateDateTime’, SERVERPROPERTY(‘ResourceLastUpdateDateTime’)

UNION ALL

SELECT ‘ResourceVersion’, SERVERPROPERTY(‘ResourceVersion’)

UNION ALL

SELECT ‘ServerName’, SERVERPROPERTY(‘ServerName’)

UNION ALL

SELECT ‘SqlCharSet’, SERVERPROPERTY(‘SqlCharSet’)

UNION ALL

SELECT ‘SqlCharSetName’, SERVERPROPERTY(‘SqlCharSetName’)

UNION ALL

SELECT ‘SqlSortOrder’, SERVERPROPERTY(‘SqlSortOrder’)

UNION ALL

SELECT ‘SqlSortOrderName’, SERVERPROPERTY(‘SqlSortOrderName’)

UNION ALL

SELECT ‘FilestreamShareName’, SERVERPROPERTY(‘FilestreamShareName’)

UNION ALL

SELECT ‘FilestreamConfiguredLevel’, SERVERPROPERTY(‘FilestreamConfiguredLevel’)

UNION ALL

SELECT ‘FilestreamEffectiveLevel’, SERVERPROPERTY(‘FilestreamEffectiveLevel’)

If you want to check what all these different Server Property means, please also see http://msdn.microsoft.com/en-us/library/ms174396.aspx

Good Luck!

SQL Server – List all Foreign Key Constraints of Database or Table

List all Foreign Key Constraints of Database or Table

How often we face this problem? The Foreign Key constraint of the table preventing us from deleting or modifying records.

To deal with them, first we need to have information about these constraints. How nice it would it be to get details of all the Foreign Key constraint defined in the Database or on a Table.

Good News, yes we can. Just use following script:

USE AdventureWorks

– List all Foreign Key Constraints in whole Database

SELECT o1.name AS Referencing_Object_name,

c1.name AS referencing_column_Name,

o2.name AS Referenced_Object_name,

c2.name AS Referenced_Column_Name,

s.name AS Constraint_name

FROM sysforeignkeys fk

INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id

INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id

INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey

INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey

INNER JOIN sysobjects s ON fk.constid = s.id


– List all Foreign Key Constraints in Table Customer

SELECT o1.name AS Referencing_Object_name,

c1.name AS referencing_column_Name,

o2.name AS Referenced_Object_name,

c2.name AS Referenced_Column_Name,

s.name AS Constraint_name

FROM sysforeignkeys fk

INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id

INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id

INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey

INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey

INNER JOIN sysobjects s ON fk.constid = s.id

AND o2.name=‘Customer’

See also:

SQL Server – List all Constraints of Database or Table

SQL Server – Drop All Table Constraints

Good Luck!

SQL Server – Drop All Table Constraints

Drop All Table Constraints

You want to redefine all the constraints set on a Table. To do that you need to first drop all the existing constraints set on the table. Following is the simple script to do so the same:

– To drop all Constraints on a table Employee

DECLARE @database NVARCHAR(50)

DECLARE @table NVARCHAR(50)

DECLARE @sql NVARCHAR(255)

SET @database = ‘DB_MyDatabase’

SET @table = ‘Employee’

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table)

BEGIN

SELECT @sql = ‘ALTER TABLE ‘ + @table + ‘ DROP CONSTRAINT ‘ + CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table

EXEC sp_executesql @sql

END

The above script does not drop DEFAULT constraint set on the Table.

See also:

SQL Server – List all Constraints of Database or Table

SQL Server – List all Foreign Key Constraints of Database or Table

Good Luck!

Follow

Get every new post delivered to your Inbox.