Find Table definition SQL Server

 There is few way to get the table information like below.

1. 

Sp_help 'TableName' 

2. 

SELECT
COLUMN_NAME, DATA_TYPE,character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'TableName' 

Convert Rows to Comma Seperated Column 

DECLARE @Names VARCHAR(MAX) = '';
SELECT
    --COALESCE(clmns.name , ',')
	@Names = COALESCE(@Names + ', ', '') + clmns.name 
    FROM
    sys.tables AS tbl
    INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
    LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
	LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id 
    and cik.column_id = clmns.column_id 
    and cik.object_id = clmns.object_id and 0 = cik.is_included_column
 where tbl.name = N'TableName'-- AND is_identity != 1
 SELECT @Names;

Create and Insert N rows in sql without user interface

 To insert bulk data for testing purpose we can use below query. this query create the table and insert the rows and if required delete the table too.

CREATE TABLE TEST1 (TEST_ID INT IDENTITY(1,1), Name varchar(50), InsDate datetime)

SET IDENTITY_INSERT TEST1 ON;

DECLARE @numRows int,@i int

SET @numRows = 10000

SET @i=1

Declare @Prefix varchar(50) = '_';

WHILE @i<@numRows

BEGIN

Set @Prefix += Cast(@i as varchar(50));

if RIGHT(Cast(@i as varchar(50)), 1) = '0' Set @Prefix = '_';

    INSERT TEST1(TEST_ID,Name,InsDate) SELECT @i,('Murli' + @Prefix),Getdate()

    SET @i=@i+1

END

SET IDENTITY_INSERT TEST1 OFF;

SELECT * FROM TEST1

--DROP TABLE TEST1

Get Latest Active table Name

 Get the latest activity happed on which table name either insert, delete or update.

SELECT 

    t.name AS TableName,

    us.last_user_update

FROM 

    sys.dm_db_index_usage_stats AS us

INNER JOIN 

    sys.tables AS t ON t.object_id = us.object_id

WHERE 

    us.database_id = DB_ID()

ORDER BY 

    us.last_user_update DESC;