Friday, June 5, 2009

Handy SQL Queries

Get all columns in a table
SELECT name FROM syscolumns WHERE OBJECT_NAME(id) = 'table_name'

Get all tables in a database
SELECT name FROM syscolumns WHERE xtype = 'U'

Gat all stored procedures modified after a certain date
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND modify_date > '12/1/2009'
order by name

Other types are:
'V' - Views
'S' - System Tables
'P' - Stored Procedures
'F' - Foriegn Keys
'UQ' - Indexes
'D' - Contstraints
'PK' - Primary Keys
'FN' - Scalar-valued Functions
'TF' - Table-valued Functions

Get the space used by database/individual table
For database:
exec sp_spaceused

For table:
exec sp_spaceused N'table_name'

Add Linked Server
EXEC sp_addlinkedserver @server='name_of_connection',

Add Linked Server Login
EXEC sp_addlinkedsrvlogin @rmtsrvname='name_of_connection',

List Linked Servers
EXEC sp_helpserver

No comments:

Post a Comment