Getting all the tables, functions and views in SQL using ADO.NET.

public void LoadObjects()

{

try

{

using ( SqlConnection conn = new SqlConnection( connString ) )

{

conn.Open();

using ( SqlCommand command = conn.CreateCommand() )

{

// get the tables

// get the views

// get the stored procs

// get the functions

command.CommandText = "select name, id from sysobjects where xtype='U'";

using( SqlDataReader reader = command.ExecuteReader() )

{

while( reader.Read() )

{

UserTable t = new UserTable( reader.GetString( 0 ), reader.GetInt32( 1 ) );

userTables[t.Name] = t;

}

}

command.CommandText = "select name, id from sysobjects where xtype='V' and category=0";

using( SqlDataReader reader = command.ExecuteReader() )

{

while( reader.Read() )

{

View v = new View( reader.GetString( 0 ), reader.GetInt32( 1 ) );

views[v.Name] = v;

}

}

command.CommandText = "select name, id from sysobjects where xtype='P' and category=0";

using( SqlDataReader reader = command.ExecuteReader() )

{

while( reader.Read() )

{

StoredProc sp = new StoredProc( reader.GetString( 0 ), reader.GetInt32( 1 ) );

storedProcs[sp.Name] = sp;

}

}

command.CommandText = "select name, id from sysobjects where xtype='FN' and category=0";

using( SqlDataReader reader = command.ExecuteReader() )

{

while( reader.Read() )

{

Function f = new Function( reader.GetString( 0 ), reader.GetInt32( 1 ) );

functions[f.Name] = f;

}

}

}

// gather the data for the tables, views, procs and functions

foreach( UserTable t in userTables.Values )

t.GatherData( conn );

foreach( View v in views.Values )

v.GatherData( conn );

foreach( StoredProc sp in storedProcs.Values )

sp.GatherData( conn );

foreach( Function f in functions.Values )

f.GatherData( conn );

conn.Close();

}

}

catch ( Exception ex )

{

throw ex;

}

}

using the above code you can get all the tables, functions and views database objects using ADO.NET.

Mentioned below are the code snippets to get all the indexes.

SELECT '['+dbschemas.name+'].['+ dbtables.[name]+']' AS TableName,

dbindexes.[name] as 'Index',dbindexes.type_desc,

indexstats.avg_fragmentation_in_percent,

indexstats.page_count,

SUBSTRING(( SELECT ', ' + AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I

ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC

ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC

ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE dbindexes.[object_id] = I.[object_id]

AND dbindexes.index_id = I.index_id

AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR

XML PATH('')

), 2, 8000) AS KeyCols,

SUBSTRING(( SELECT ', ' + AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I

ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC

ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC

ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE dbindexes.[object_id] = I.[object_id]

AND dbindexes.index_id = I.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR

XML PATH('')

), 2, 8000) AS IncludeCols

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE dbtables.name in ({0})

ORDER BY indexstats.avg_fragmentation_in_percent desc

";