SQL scripts repo

Just sharing a collection of SQL commands which we can use for various information retrieving w.r.t databases and tables in an SQL instance. Will try to keep this post updated with the commands I encounter. 

Go here... https://github.com/dax516/SQL/    

• One example is as below is a command to check the number of tables in a particular database
  USE [YOUR DATABASE NAME]
  SELECT COUNT(*) from information_schema.tables
  WHERE table_type = 'base table' 

Happy coding! 

Rebuild index for all tables in a SQL Express DB

I have this weird situation in one of my ongoing projects, where there are several hundreds of SQL Express instances hosting Dynamics AX Channel Databases which are spread across few high performance virtual servers. Over a time we noticed that Server performance has reduced and a result of a good meeting with all the parties involved - we identified that SQL index rebuild was never performed even with loads of transactions and master data has been moving via the Channel databases. 

The obvious reason being - the SQL instances are Express and it doesn't support SQL Agent - to simply configure a Rebuild job out of the box. The below script can be helpful in order: 

1. Get the current status of Index update
2. Update indexes for all objects in all databases in a particular SQL instance. 

https://github.com/dax516/SQL/blob/master/DatabaseLevelIndexRebuild.sql

Hope this helps. Happy coding!