Find the user who dropped database table

First, let us drop the table by following command.

DROP TABLE TempTable
GO

Now the challenge is how to find out who dropped the table which was created in the database.

There is a simpler way to do the same.

Right click on the database then Go to Reports -> Standard Reports -> Schema Changes History.

Once you open Schema Changes History, you will see a report. Over there, expand any object which you are interested in and you will see necessary details associated with the same.

For ex., I am interested in TempTable and you can see it shows the three rows of schema change. You can also see the name of the user and time when he/she dropped the table.

How to find Column Name and it's Data Type in Sql Server

Following will show you the column list with their datatype of a table in SQL Server.

select col.name as 'ColumName',typ.name as 'DataType' from sys.columns col left join sys.types typ
on col.system_type_id=typ.system_type_id  where object_id=object_id('login')

That will show you the result as below:

What is the difference between Scope_Identity(), Identity(), @@Identity??

Most of the developers get confused in these terms:

1. @@identity always returns the last identity value created in the same session regardless of the table that produced the value, and regardless of the scope of the statement.
2. scope_identity() returns the identity value created in the same session and the same scope.
3. identity() is not used to get an identity value but instead is used to create an identity in select...into statement that is declaring a column in a table as an identity column.

Here the session means the database connection.

Here is an example:

CREATE TABLE ABC(
  ID INT IDENTITY(1,1),
  Name VARCHAR(100)
)

INSERT INTO ABC (Name) VALUES ('x')
SELECT SCOPE_IDENTITY(), @@IDENTITY

Note:
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

What are the advantages using LINQ than stored procedure

There are a lot:

1. Debugging - It is really very hard to debug the Stored procedure but as LINQ is part of .NET, you can use visual studio's debugger to debug the queries.
2. Deployment - With stored procedures, we need to provide an additional script for stored procedures but with LINQ everything gets complied into single DLL hence deployment becomes easy.
3. Type Safety - LINQ is type safe, so queries errors are type checked at compile time. It is really good to encounter an error when compiling rather than runtime exception!
4. Built-in security - One reason I preferred stored procs before LINQ was that they forced the use of parameters, helping to reduce SQL injection attacks. LINQ to SQL already parameterizes input, which is just as secure.
5. Reduction in work - Before LINQ, I spent a lot of time building DALs, but now my DataContext is the DAL. I've used OPFs too, but now I have LINQ that ships with multiple providers in the box and many other 3rd party providers, giving me the benefits from my previous points.

NOTE: If all you're doing is simple INSERT, UPDATE, and DELETE statements LINQ is the way to go (in my opinion) and all the optimization is done for you, for more complex work I would say to stick with stored procedures.
For more detailed info, refer this: What is the advantage of LINQ over stored procedures?