Sunday, September 22, 2013

things to remember while using SQL Server

For SQL SERVER ONLY


Sql Server Links

Find All Triggers particular to database

select so.name, text
from sysobjects so, syscomments sc
where type = 'TR'
and so.id = sc.id
and text like '%YoutTableName%'

All Trigger In the DataBase Sql Server

1.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR'
2.
SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'


For Sql Server 2000


SELECT 
     o.name AS trigger_name 
    ,'x' AS trigger_owner 
    /*USER_NAME(o.uid)*/ 
    ,s.name AS table_schema 
    ,OBJECT_NAME(o.parent_obj) AS table_name 
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS o 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sysobjects AS o2 
    ON o.parent_obj = o2.id 

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

Find references of tables,stored proc,functions,...etc

----Option 1SELECT DISTINCT so.nameFROM syscomments scINNER JOIN sysobjects so ON sc.id=so.idWHERE sc.TEXT LIKE '%tablename%'----Option 2SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE c.TEXT LIKE '%tablename%'

Find lock on Table and kill lock on table in sql server


 SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type,
    request_session_id
FROM
    sys.dm_tran_locks L
    join sys.partitions P
ON L.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'MstPD_CADNo'

Kill request_session_id;

add linked server in sql server


Exec sp_addlinkedserver 
@server ='MWNJDB',
@srvproduct ='' ,
@provider ='SQLNCLI',
@datasrc ='MWNJDB',
@location ='',
@provstr ='',
@catalog =''

find duplicate rows...in sql server


Sql server Import CSV File



DECLARE @tableName varchar(10);

DECLARE @importFile varchar(10),@importQuery varchar(100);

set @importFile='France_AD_Rates.csv';

SET @tableName = '#France_AD_Rates';

SET @importQuery= 'France_AD_Rates.csv';

EXEC( 'SELECT * INTO ' + @tableName + 'from OpenRowset(
''MSDASQL'',''Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=D:\;'',''SELECT * FROM ' + @importFile + ''')')

Csv file import in sql server


SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=D:\;','SELECT * FROM France_AD_Rates.csv')


Replacement code for Cursor Using Temp Tables.
There Should be 1 Primary Key In the Table.


Declare @id varchar(100)
 select CADNo into #TempOtherRing from ##tempHead ;

 while exists(select CADNo from #TempOtherRing)                                                  
  BEGIN                                
SELECT TOP 1 @id=CADNo  FROM #TempOtherRing;  
-----Here From Your Primary Key You Can Fetch any Record in the table.
----CADNo  is primary key in my table.
----Fetch Records from Main Table Using CADNo.
----Perform Your Operations.
 delete from #TempOtherRing  where CADNo=@id;      
 END



 http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries-are-bad-index-coverage.aspx

For Millions of Record Use full-text index On DataBases

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

Indexing In Sql Server 
From Basic To Expert
http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server

https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

http://www.sqlteam.com/article/sql-server-indexes-the-basics

http://odetocode.com/articles/70.aspx

http://msdn.microsoft.com/en-us/library/ms190457.aspx

Visual Representation of SQL JOINS

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

SQL Server Optimization 
http://technet.microsoft.com/en-us/library/aa964133%28v=sql.90%29.aspx

SQL SERVER – Shrinking Truncate Log File – Log Full
use DFE2
Go
ALTER DATABASE DFE2 SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(ALLCO_Log, 1)
ALTER DATABASE DFE2 SET RECOVERY FULL WITH NO_WAIT

USE DatabaseName
GODBCC SHRINKFILE(<TransactionLogName>, 1)BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLYDBCC SHRINKFILE(<TransactionLogName>, 1)GO


Database Talks while Making Connection Using Sql Server with Dot Net

http://www.mstecharticles.com/

Diff Between Candidate-Keys-and-Primary-Key
http://dotnetslackers.com/articles/sql/Difference-Between-Candidate-Keys-and-Primary-Key.aspx



AdventureWorks Databases – 2012, 2008R2 and 2008

http://msftdbprodsamples.codeplex.com/releases/view/93587

Connection String For SQL SERVER 2008 LOCAL

Data Source=.;Initial Catalog=AdventureWorks2008R2;User ID=sa;Password=sqlserver



Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)