Microsoft SQL (MSSQL)

These are Microsoft SQL commands, special tables, queries, etc. that I don’t want to forget:

Select all tables that have a column with a specific name

from sys.tables t, sys.columns c
where t.object_Id = c.object_id
order by

Select the columns of a table

from sys.tables t, sys.columns c
where t.object_Id = c.object_id

Get the create statement that created a view:

sp_helptext 'VIEW_NAME'

Get the parameter, return, and column datatypes of a table, view, or stored procedure:

sp_help 'OBJECT_NAME'

select store procedure and function code

select * from syscomments

map a database user to a sql server login:

alter user [UserName] with login = [UserName]

get info about an object (stored proc, table, view)

sp_help object_name

Querying for a table name

select name
from sysobjects
where lower(name) like '%campus%' and xtype = 'U' order by name
-- you could also use the sys.tables table

Equivalent of nvl in oracle

select isnull(value1, value2)

disable a constraint

alter table [TableName] nocheck constraint [ConstraintName] -- I've used it on Foreign Keys

enable a constraint

alter table [TableName] check constraint [ConstraintName] -- I've used it on Foreign Keys

Run all constraints and see if one of them breaks. Check constraints are only executed on newly inserted data. So if you disable a constraint, then enter invalid data, and then enable the constraint, you would never know that there is invalid data unless you run this command. THIS COMMAND ONLY CHECKS THE CONSTRAINTS THAT ARE ENABLED so make sure you are “checking” all constraints before you run it.

dbcc checkconstraints

Find foreign keys for any table in database

SELECT AS ForeignKey,
	OBJECT_NAME(f.parent_object_id) AS TableName,
	COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
	OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
	COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
	ON f.object_id = fc.constraint_object_id
    and object_name(f.referenced_object_id) = 'TABLE NAME HERE'

query the body of a stored procedure or function

select name, xtype, crdate, definition
from sysobjects obj
left outer join sys.sql_modules md on = md.object_id
where xtype not in ('U', 'V', 'PK', 'D', 'F', 'TR')
--and name like '%met%'
--and name like '%PreReq%'
and md.definition like '%AdCoursePreReqDetail%'
order by name

Examples of “complex” update statement. Note that amhighschool was implicityly added to the from clause

update amhighschool
set code = dups.code
from pedrotemp_dups dups
where amhighschool.amhighschoolid = dups.amhighschoolid
update systudentinquiry
set amrepid = s.amrepid
from systudentinquiry si, systudent s -- if you want to set an alias to the systudentinquiry table, you may do so by explicitly referencing the table
where si.systudentid = s.systudentid

The query below shows the number of connections on the DB server. The query is run on the “master” database:

  DB_NAME(dbid) as DBName, 
  COUNT(dbid) as NumberOfConnections,
  loginame as LoginName
  dbid > 0
  dbid, loginame

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: