Wednesday, May 03, 2006

Good SQL Server Commands

sp_who --gets all the users connected to sqlserver

select name from syscolumns where id = object_id('job') --gets only column names

select name from sysindexes where id=object_id('job') --gets only index names

select groupname from sysfilegroups -- list all the filegroups

select name, user_name(uid) from sysobjects where type='U' --list all the userdefined table in the database

select, * from syscolumns, sysobjectswhere'Dated'and = syscolumns.idand (sysobjects.xtype='U' or sysobjects.xtype='S') --select all tables containing the given column name

/* Move the Database */Use multi1GoExec sp_helpfileGo--Exec sp_detach_db 'MyDatabase'Go--Exec sp_attach_db 'MyDatabase','E:\MsSql7\NewHome\MyDatabase_Data.mdf','E:\MsSql7\NewHome\MyDatabase_Log.ldf'

--get the min, max and avg length of a column
Select min(datalength(dated)),avg(datalength(dated)),max(datalength(dated)) from job;

-- Checks the database integrity of a given database. with no_infomsgs can be removed
dbcc checkdb(multi1) with no_infomsgs

/* Check if a trigger exists */
if exists (select * from sysobjects where name='tD_Employee' and xtype='TR') print 'Trigger exists'else print 'Trigger does not exist'

/* get the details of current connection */
sp_who @@SPID


select nt_username, hostname, nt_domain, loginame, login_time, program_name from master..sysprocesses where spid=53

SELECT @@version --gets the current service pack installed

No comments: