Find relations between the tables

when you join a new company first thing you need to know about the tables and its relationships .This query will help to know the relations of the all the tables .Just select the database and run this query.

Tips: Export the result in excel and filters out the columns it is easy to find relations.


SELECT Object_name(rkeyid)  Parent_Table, 
       Object_name(fkeyid)  Child_Table, 
       Object_name(constid) FKey_Name, 
       c1.NAME              FKey_Col, 
       c2.NAME              Ref_KeyCol 
FROM   sys.sysforeignkeys s 
       INNER JOIN sys.syscolumns c1 
               ON ( s.fkeyid = c1.id 
                    AND s.fkey = c1.colid ) 
       INNER JOIN syscolumns c2 
               ON ( s.rkeyid = c2.id 
                    AND s.rkey = c2.colid ) 
ORDER  BY parent_table, 
          child_table 

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

When you facing this issue its means the timeout is expire.In your code where you run the stored procedure you should have something like this:

SqlCommand c = new SqlCommand(...)

//...

Add such a line of code:

c.CommandTimeout = 0;

This will wait as much time as needed for the operation to complete.

Ref:

http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation

How to get Multiple Insert or Update ID

Recently i assign a task in which I need to log every record that is inserted or updated in table and insert in to second table . we can do it by using trigger but in my case there are some limitation so i am using this approach  .

In my case there is a store procedure that is doing is used to insert or update the table,

So i make a this
ALTER PROCEDURE [Sp_name] 
AS 
  BEGIN 
      DECLARE @Last_Insterted_Id INTEGER=0 
      DECLARE @Updated_Id INTEGER=0 
      DECLARE @listStr NVARCHAR(max) 

      INSERT INTO [table] 
                  (col) 
      VALUES      (values) 

      -- LOG 
      INSERT INTO [table] 
                  (col) 
      output      inserted.id 
      INTO @mytable -- inserted id  
      SELECT @Last_Insterted_Id = id 
      FROM   @mytable -- the Inserted id      
      -- In update case you need to get the IDS 
      UPDATE [table_name] 
      SET    a = @A 
      output inserted.id 
      INTO @mytable -- get the updated id      
      WHERE  col = @A 

      SELECT @Updated_Id = id 
      FROM   @mytable -- the updated id   
      SELECT col 
      FROM   table 
      WHERE  admin_user_rights_id = @Updated_Id 
  -- in which you get the recently update id 
  END 


If in case you have multiple Ids so we will think for cursor use these ids and insert or update right ?
:) I use the other approach .If we are inserting/updating multiple ids we get in   @mytable

so we can separate with comma use IN clause and get all the columns

       
SELECT @listStr = COALESCE(@listStr+',' ,'') +convert(nvarchar(20), Id)
FROM @mytable
SELECT @listStr


INSERT INTO table_name 
            (col) 
SELECT col 
WHERE  id IN(SELECT data 
             FROM   dbo.Split(@listStr, ',')) -- in this we get all the ids with comma separate 

use same for update ...


P.S i try to start blogging may be i am not able try to explain perfectly but may be this help for you

Refernce links

OUTPUT Keyword
http://stackoverflow.com/questions/12566493/how-can-i-assign-inserted-output-value-to-a-variable-in-sql-server


COALESCE Keyword
https://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/