Microsoft SQL (MSSQL)

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

Create a #Numbers helper table to make complex aggregate queries

SELECT TOP 200 N=IDENTITY(INT, 1, 1)
INTO #Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;
ALTER TABLE #Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);
SELECT TOP 5 * FROM #Numbers ORDER BY N desc;
--DROP table #Numbers

Select all tables that have a column with a specific name

select t.name
from sys.tables t, sys.columns c
where t.object_Id = c.object_id
and c.name = 'COLUMN NAME HERE'
order by t.name

Select the columns of a table

select c.name
from sys.tables t, sys.columns c
where t.object_Id = c.object_id
and t.name = 'TABLE NAME HERE'

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	f.name 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 obj.id = 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:

SELECT
  DB_NAME(dbid) as DBName,
  COUNT(dbid) as NumberOfConnections,
  loginame as LoginName
FROM
  sys.sysprocesses
WHERE
  dbid > 0
GROUP BY
  dbid, loginame