How to find the IP address and Machine name of SQL Server ?

-- IP address and Machine name of SQL Server
SELECT Connectionproperty('local_net_address')       AS "IP Address",
       Serverproperty('ComputerNamePhysicalNetBIOS') "Machine Name"

-- Client and Server Ip Address and Mahine name
SELECT Serverproperty('ComputerNamePhysicalNetBIOS') "Machine Name",
       local_net_address                             AS "Server IP Address",
       client_net_address                            AS "Client IP Address"
FROM   SYS.DM_EXEC_CONNECTIONS
WHERE  SESSION_ID = @@SPID

How to find the Number of records in a Table ?

-- To fetch the number of records in a table
SELECT ss.name            AS [schema],
       Object_name(si.id) AS TableName,
       Max(si.rowcnt)     AS EstimatedRows
FROM   dbo.sysindexes si
       INNER JOIN sys.tables st
               ON st.object_id = si.id
       INNER JOIN sys.schemas ss
               ON ss.schema_id = st.schema_id
WHERE  indid < 2 -- clustered index or table entry
       AND Objectproperty(si.id, 'IsUserTable') = 1
       AND Objectproperty(si.id, 'IsMSShipped') = 0
       AND Permissions(id) != 0 -- User must have at least some privileges on the object.
       AND Object_name(si.id) LIKE '%Table Name%'
GROUP  BY Object_name(si.id),
          ss.name

ORDER  BY 2

How to fetch the database restore Information ?

-- DB last restore Date

WITH RestoreInformation AS
(
SELECT
    d.name "Database Name",
    d.create_date ,
    d.compatibility_level ,
    d.collation_name ,
    rh.*,
     ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY rh.restore_date DESC)  "RowNumber"
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = d.Name where  d.name ='Database Name'
)
SELECT *
FROM RestoreInformation
WHERE RowNumber = 1


--To fetch the DB restore History
SELECT
    d.name "Database Name",
    d.create_date ,
    d.compatibility_level ,
    d.collation_name ,
    rh.*,
     ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY rh.restore_date DESC)  "RowNumber"
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = d.Name
where d.name ='Database Name' order by RowNumber asc

What is the difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT ?

@@IDENTITY ==> Is a system function that returns the last-inserted identity value for any table in the current session, across all scopes.

SCOPE_IDENTITY() ==> Returns the last identity value inserted into an identity column for any table in the current session and in the same scope.

IDENT_CURRENT ==> Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

GO

-- create a main table

CREATE TABLE test_main(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- insert some records

INSERT INTO test_main(name) VALUES('A'),('B'),('C')

GO

-- create child table

CREATE TABLE test_child(id INT IDENTITY (1,1), name VARCHAR(10))

GO

-- create a trigger on main table to insert the reccords into child table

CREATE TRIGGER test_trigger ON test_main FOR INSERT

AS

BEGIN

INSERT INTO test_child SELECT name FROM INSERTED

END

GO

-- check the identity on main table

SELECT * FROM test_main

-- result

1 A

2 B

3   C

GO

-- now insert records into main table and check the identity

INSERT INTO test_main(name) SELECT 'D'

-- now check the below result

select @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT('test_main')

-- result

1 4 4

Useful System Procedures :

sp_help ==> To get a table / object definition

Example :   EXEC sp_help object_name

sp_helptext ==> To get the procedure / view text.

Example :  EXEC sp_helptext Proc_name

sp_helpindex ==> To get the index information on a table

Example :  EXEC sp_helpindex Proc_name

sp_helpconstraint ==> To get the constarints on a table

Example :  EXEC sp_helpconstraint Proc_name

sp_depends ==> To get the dependencies on table / procedure / view.

Example :  EXEC sp_depends object_name

sp_spaceused ==> To get the size of a table or database, if you didn`t pass any input it will show the size of the current database.

Example :  EXEC sp_spaceused table_name

sp_helptrigger  ==> To get the triggers on a table

Example :  EXEC sp_helptrigger table_name

sp_rename ==> To rename database objects and columns.

Example : EXEC sp_rename 'table_name' , 'new_table_name'
     EXEC sp_rename 'table_name.column_name' , 'new_column_name'

sp_tables ==> To get the tables with the name like a given syntax / pattern.

Example : EXEC sp_tables '%user%'

sp_stored_procedures ==> To get the stored procedure names with the name like a given syntax / pattern.

Example : EXEC sp_stored_procedures '%user%'