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

11 comments:

  1. Subhash.CApril 4, 2012 at 7:09 AM

    How get Multiple Rows Values into a Single Row

    Create 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

    ReplyDelete
  2. Subhash.CApril 4, 2012 at 10:35 PM

    How to check Duplicate record before inserting into Database.(If no Constraints Concepts)

    EID, 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.

    ReplyDelete
  3. Subhash.CApril 4, 2012 at 10:40 PM

    How to calculate hour/Minutes/Seconds between two Conversation
    Select OperatorID,CalledDate,CallStTime,CallEndTime,
    CAST(CallEndTime as datetime) - CAST( CallStTime AS datetime)as Actualtime,
    D.DESCRIPTION from OperatorCallDetails

    ReplyDelete
  4. Subhash.CApril 6, 2012 at 10:25 PM

    How to remove Duplicate Rows
    create 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

    ReplyDelete
  5. Subhash.CApril 16, 2012 at 10:36 PM

    Check How many tables in Your Database :

    USE (Database Name)
    SELECT COUNT(*) from information_schema.tables
    WHERE table_type = 'base table'

    ReplyDelete
  6. Subhash.CApril 19, 2012 at 3:46 AM

    Select-Unique-row-element-from-a-table
    create 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

    ReplyDelete
  7. Subhash.CApril 21, 2012 at 3:13 AM

    Re: Needed query to check any time interval collides or not
    a few seconds ago|LINK

    Try this.....it will show if collied.

    Select starttime,endtime from TABLE Group By starttime,endtime having COUNT(*) > 1

    ReplyDelete
  8. Subhash.CApril 27, 2012 at 11:25 PM

    how to change the active status column in this table ?
    4/28/2012 4:00:53 AM

    You can do that by this query

    sp_rename 'dbo.TableName.OldName','New Name'

    ReplyDelete
  9. Subhash.CMay 2, 2012 at 6:17 AM

    The data types ntext and varchar are incompatible in the equal to operator.

    Try this sure help you :

    set Description='No Values' Where cast(Description as varchar(20)) ='' or Description IS NULL

    And you can write both lines.

    ReplyDelete
  10. Subhash.CJuly 18, 2015 at 3:30 AM

    Finding Locking & Blocking……..sys.dm_tran_locks

    SELECT
    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

    ReplyDelete

Subscribe to: Posts (Atom)