SQL Server
Post Your Question on SQL Server Only.
.
The following table maps the system tables or functions that live in every database in SQL
fn_virtualfilestats
syscolumns
syscomments
sysconstraints
sysdepends
sysfilegroups
sysfiles
sysforeignkeys
sysindexes
sysindexkeys
sysmembers
sysobjects
syspermissions
sysprotects
sysreferences
systypes
sysusers
sysfulltextcatalogs
How get Multiple Rows Values into a Single Row
ReplyDeleteCreate table #Temp (COMPANIES varchar(10))
INSERT INTO #Temp VALUES('IBM');
INSERT INTO #Temp VALUES('HCL');
INSERT INTO #Temp VALUES('WIPRO');
INSERT INTO #Temp VALUES('IENERGY');
INSERT INTO #Temp VALUES('SAPIENT');
SELECT DISTINCT STUFF( (SELECT ',' + COMPANIES from #Temp FOR XML PATH('')),1,1,'')
as Numbers FROM #Temp
DROP table #TEMP
How to check Duplicate record before inserting into Database.(If no Constraints Concepts)
ReplyDeleteEID, NAME, PLACE-Tables fields
if exists (select 1 from TABLE where EID=110)
return
insert into TABLE values (111,'K.SHARMA','GOA')
Pick any thing from your new Records to Varify like i Used EID.
How to calculate hour/Minutes/Seconds between two Conversation
ReplyDeleteSelect OperatorID,CalledDate,CallStTime,CallEndTime,
CAST(CallEndTime as datetime) - CAST( CallStTime AS datetime)as Actualtime,
D.DESCRIPTION from OperatorCallDetails
have a good day..
DeleteHow to remove Duplicate Rows
ReplyDeletecreate table Duplicate (ID int, Names varchar(20), Age int)
insert Duplicate select 1,'AA',1
insert Duplicate select 2,'BB',2
insert Duplicate select 1,'AA',1
insert Duplicate select 4,'KK',7
insert Duplicate select 2,'CC',3
insert Duplicate select 2,'BB',2
insert Duplicate select 3,'D',2
insert Duplicate select 3,'D',2
drop table Duplicate
select * from Duplicate
select id,names,age from Duplicate group by id,names,age having count(*)>1
set rowcount 1
select 1
while @@rowcount > 0
delete Duplicate
where 1 < (select count(*) from Duplicate a2 where Duplicate.id = a2.id and Duplicate.names = a2.names and Duplicate.age = a2.age)
set rowcount 0
Check How many tables in Your Database :
ReplyDeleteUSE (Database Name)
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'
Select-Unique-row-element-from-a-table
ReplyDeletecreate table #a (i int, j int)
(then insert Your Values)
select * into #b from #a where 1 = 0
insert #b select i,j from #a group by i,j having count(*) > 1
begin tran
delete #a from #b where #a.i = #b.i and #a.j = #b.j
and #a.i = #b.j and #a.j = #b.i and #b.i=#a.j and #b.j=#a.i
insert #a select * from #b
commit tran
select * from #A
select * from #b
drop table #A
drop table #b
Re: Needed query to check any time interval collides or not
ReplyDeletea few seconds ago|LINK
Try this.....it will show if collied.
Select starttime,endtime from TABLE Group By starttime,endtime having COUNT(*) > 1
how to change the active status column in this table ?
ReplyDelete4/28/2012 4:00:53 AM
You can do that by this query
sp_rename 'dbo.TableName.OldName','New Name'
The data types ntext and varchar are incompatible in the equal to operator.
ReplyDeleteTry this sure help you :
set Description='No Values' Where cast(Description as varchar(20)) ='' or Description IS NULL
And you can write both lines.
Finding Locking & Blocking……..sys.dm_tran_locks
ReplyDeleteSELECT
CASE DTL.REQUEST_SESSION_ID
WHEN -2 THEN 'ORPHANED DISTRIBUTED TRANSACTION'
WHEN -3 THEN 'DEFERRED RECOVERY TRANSACTION'
ELSE DTL.REQUEST_SESSION_ID END AS SPID,
DB_NAME(DTL.RESOURCE_DATABASE_ID) AS DATABASENAME,
SO.NAME AS LOCKEDOBJECTNAME,
DTL.RESOURCE_TYPE AS LOCKEDRESOURCE,
DTL.REQUEST_MODE AS LOCKTYPE,
ST.TEXT AS SQLSTATEMENTTEXT,
ES.LOGIN_NAME AS LOGINNAME,
ES.HOST_NAME AS HOSTNAME,
CASE TST.IS_USER_TRANSACTION
WHEN 0 THEN 'SYSTEM TRANSACTION'
WHEN 1 THEN 'USER TRANSACTION' END AS USER_OR_SYSTEM_TRANSACTION,
AT.NAME AS TRANSACTIONNAME,
DTL.REQUEST_STATUS
FROM
SYS.DM_TRAN_LOCKS DTL
JOIN SYS.PARTITIONS SP ON SP.HOBT_ID = DTL.RESOURCE_ASSOCIATED_ENTITY_ID
JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = SP.OBJECT_ID
JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = DTL.REQUEST_SESSION_ID
JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TST ON ES.SESSION_ID = TST.SESSION_ID
JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS AT ON TST.TRANSACTION_ID = AT.TRANSACTION_ID
JOIN SYS.DM_EXEC_CONNECTIONS EC ON EC.SESSION_ID = ES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST
WHERE
RESOURCE_DATABASE_ID = DB_ID()
ORDER BY DTL.REQUEST_SESSION_ID