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
";