I have MS SQL Server 2008 R2 database. How to add MS SQL Table using python console in QGIS?
Each data provider has its URI and its provider key. Once added via User Interface, it's easy to collect them and use them from the console.
Enter this statement in your python console, to collect the URI and key for all map layers:
to create a new map layer based on this information:
The key for MS SQL is 'mssql'
The uri follow the QgsDataSourceURI standard, which has an example in the PyQGIS cookbook chapter Loading Layers, Vector Layers. MS SQL works analogue to postgres.
The above example duplicates just one layer, but prints the necessary information to add each of them and gives an idea about the principle.