DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them, but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

Overview of SQL Server Management Studio 2012

Tag: IDE, panes, SQL Server Management Studio, SSMS, SSMS, Development

Here's a 10-minute crash course in SQL Server Management Studio 2012. In addition to showing you the new undockable panes, I get into some of the tried and true tricks which are present in SSMS 2005, SSMS 2008 and SSMS 2008 R2.

Determine if a Column has an IDENTITY property

Tag: columnproperty, identity, object_name, syscolumns, T-SQL, Inventory

If you need to get a list of tables and the IDENTITY field in each one, use the following query.

SELECT OBJECT_NAME(id) as TableName, name as ColumnName
FROM syscolumns
WHERE status = 0x80

An alternative way to find the IDENTITY property is to use the COLUMNPROPERTY.

SELECT OBJECT_NAME(id) as TableName, name as ColumnName
FROM syscolumns
WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1

Determine if a Table has an IDENTITY property

Tag: identity, T-SQL, Inventory

To get a list of tables with a column indicating if the table has an identity field, use the following query.

select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentity
from sysobjects
where xtype = 'U'

Use Information Schema Columns view to quickly view a table definition

Tag: information schema views, T-SQL, Inventory

 

I’ll highlight the .COLUMNS view by demonstrating some of the useful information contained therein.  Here’s an example query which displays and parses information from this view in a helpful way.  Obviously you can simply use SELECT * FROM INFORMATION_SCHEMA.COLUMNS to see all the columns that are available.

 

Information Schema System View Overview

Tag: information schema views, SSMS, Inventory

 

The INFORMATION_SCHEMA views make information available that is buried in system tables or is internal to the system and not otherwise queryable.  The image below shows a listing of the view that are available.

Coalesce Basic Usage

Tag: coalesce, T-SQL, Development

The COALESCE function is very useful in selecting the first non-null in a series of arguments. A typical example might be to display a custom message in case a field is null.

COALESCE(MyManager,'I''M TOP DOG')

Note that I said a series of arguments. Unlike ISNULL() you can put as many items in the list and SQL will output the first non-NULL item on the list.

Table Variables How and Why to use them

Tag: table variable, temp table, T-SQL, Development

So what are table variables and why should I use them? There’s plenty of information out there about the table variables particularly as compared to a temporary table so let me just summarize some of the differences and point out a few use cases that are ideal for developers.
First the syntax is identical except that temporary tables are CREATED with a # while table variables are
DECLARED with a @.

CREATE TABLE #TestTable (
      ID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      SomeValue varchar(30) NOT NULL
)
 
DECLARE @TestTable TABLE (

Drag and Drop Column List into query window

Tag: SSMS, Development

 

This next tip works as long you have SSMS 2008 against any instance.  You can drag the table name, column name or even the column folder to write a query.  So if I want to write a select against a table and especially when I don’t have intellisense, I can just drag and drop.

 

Mouse over fields to see their definition

Tag: SSMS, Development

If there’s a tool I could not work without, it’s SQL Server Management Studio or SSMS for short. The tool just keeps getting better and I keep learning more about it every week. Today I pass along a simple yet otherwise unknown tip from a developer.

Using Table Aliases to Future Proof your Code

Tag: alias, SSMS, Development

When you’re dealing with a single table a table aliases is not necessary, but when you join to another table you can run into trouble. Consider the following scenario.
Your stored procedure returns a list of users and passwords as illustrated in the following SELECT statement.

SELECT
      [Server],
      [Username],
      [Password],
      [DateAdded],
      [Notes]
FROM dba.Passwords p
JOIN dba.Users u
ON p.UserID = u.UserID

Copyright © 2008-2013 SQL Tidbits™