Find the tables which does not have an Identity Key column

 
SELECT   isc.table_name
FROM     information_schema.columns AS isc
         INNER JOIN sys.tables
         ON       SYS.tables.object_id = object_id(isc.TABLE_NAME)
WHERE    SYS.tables.type               = 'u'
GROUP BY isc.table_name
HAVING   MAX(COLUMNPROPERTY( OBJECT_ID(isc.table_name), isc.column_name,'Isidentity')) =0--( To get the table which doesnot contains identity columns')
ORDER BY isc.table_name

Find the index of row in DataTable

string query = "Year = " + "2000" + " AND Period = " + "'1'";

int rowIndex = dtSaveAll.Rows.IndexOf(dtSaveAll.Select(query)(0));

Find all SQL Identity columns

 
SELECT   '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
         table_name                                                            ,
         column_name                                                           ,
         ordinal_position                                                      ,
         data_type
FROM     information_schema.columns
WHERE    columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
ORDER BY table_name


SELECT   '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
         table_name                                                            ,
         column_name                                                           ,
         ordinal_position                                                      ,
         data_type                                                             ,
         Seed_Value                               AS SeedValue                                               ,
         Increment_Value                          AS IncrementValue                                          ,
         ident_current(Object_Name(ic.Object_ID)) AS CurrentValue                                            ,
         Last_Value                               AS LastValue
FROM     information_schema.columns               AS isc
         INNER JOIN sys.identity_columns          AS ic
         ON       ic.object_id = OBJECT_ID('['+isc.table_schema+'].['+isc.table_name+']')
         INNER JOIN sys.tables
         ON       SYS.tables.object_id                                                            = ic.object_id
WHERE    columnproperty(object_id(isc.table_schema + '.' + table_name), column_name,'IsIdentity') = 1 --( To get the table which contains only identity columns')
     AND SYS.tables.type                                                                          = 'u'
ORDER BY table_name

Find table and index name for fragmented indexes

;
WITH GetFragmatation AS
     ( SELECT object_id                   AS ObjectID       ,
             OBJECT_NAME(object_id)       AS TableName      ,
             index_id                     AS IndexID        ,
             avg_fragmentation_in_percent AS PercentFragment,
             fragment_count               AS TotalFrags     ,
             avg_fragment_size_in_pages   AS PagesPerFrag   ,
             page_count                   AS NumPages
     FROM    sys.dm_db_index_physical_stats(DB_ID('RKPMS'), NULL, NULL, NULL , 'DETAILED')
     WHERE   avg_fragmentation_in_percent > 0
     )
SELECT gf.ObjectID                                              ,
       '['+SCHEMA_NAME(schema_id)+'].['+Sch.name+']' AS FullName,
       gf.TableName                                             ,
       gf.IndexID                                               ,
       IND.name AS IndexName                                    ,
       gf.PercentFragment                                       ,
       gf.TotalFrags                                            ,
       gf.PagesPerFrag                                          ,
       gf.NumPages
FROM   GetFragmatation        AS gf
       INNER JOIN sys.indexes AS ind
       ON     ind.object_id = gf.ObjectID
          AND index_id      = gf.IndexID
       INNER JOIN sys.tables AS Sch
       ON     Sch.object_id = gf.ObjectID
              --Use the below one to defragment the Index
              ALTER INDEX PK_TableNameIndexName
       ON     [dbo].[TableName]REBUILD WITH ( FILLFACTOR = 70, ONLINE = ON )

--To alter all the index for a table
--ALTER INDEX ALL ON TableName REBUILD

SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent AS [Percentage of the logical index that is fragmented], 
   IPS.avg_fragment_size_in_pages AS [Average number of pages in a leaf-level fragment.], 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count AS [Total number of records], 
   IPS.ghost_record_count,
   IPS.fragment_count AS [Number of fragments in the leaf level.], 
   IPS.avg_fragment_size_in_pages AS PagesPerFrag,
   IPS.page_count AS [Number of index or data pages.]
FROM sys.dm_db_index_physical_stats(db_id(N'Rkpms'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND ST.TYPE = 'U'
ORDER BY 4 DESC

List Everything in My Database

 
SELECT B.Name as TableName,A.name as TriggerName
FROM sysobjects A,sysobjects B
WHERE A.xtype='TR'
AND A.parent_obj = B.id

Here are the list of all possible values for this column (xtype):

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

P = Stored procedure


PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TR = Trigger

U = User table


UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure

To list the triggers

 

SELECT S2.[name] TableName, S1.[name] TriggerName, 
CASE 
WHEN S2.deltrig = s1.id  THEN 'Delete' 
WHEN S2.instrig = s1.id THEN 'Insert' 
WHEN S2.updtrig = s1.id THEN 'Update' 
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'