sql
How do I fix “Cannot connect to WMI Provider” error?
Error Message: Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. Invalid Namespace. This error is caused when the .mof files are damaged during the MS SQL Server 2005 installation. This error typically occurs [...]
Retrieve useful information from SQL Server
Today we will see how can we retrieve various information from Microsoft SQL Server using SQL queries. Get all users: SELECT * FROM sys.server_principals; Get database size: USE master GO EXEC sp_spaceused Get user privilleges for current database: USE master GO SELECT SYSOBJECTS.name AS ‘objectname’, SYSUSERS.name AS ‘username’ , Action = CASE SYSPROTECTS.action [...]
SQL column name with blank space
Let’s see how we can write queries when a column name contains spaces: Microsoft Sql Server SELECT [COLUMN Name] FROM TABLE_NAME; MySQL SELECT ‘Column Name’ FROM TABLE_NAME; Oracle SELECT "Column Name" FROM TABLE_NAME;
Retrieve useful information from MySQL
Today we will see how can we retrieve various information from mysql server using SQL queries. Get all users: SELECT * FROM mysql.USER; Get top 10 tables in size: SELECT concat(table_schema,’.',TABLE_NAME) TABLE_NAME, concat(round(data_length/(1024*1024),2),’M') data_length FROM information_schema.TABLES ORDER BY data_length DESC LIMIT 10; Get activated plugins: SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE FROM information_schema.`PLUGINS` WHERE PLUGIN_STATUS=’ACTIVE’; Get user privilleges: SELECT [...]
Backup SQL Server Database
To create a Full Database Backup using transact sql is simple. The following sql code does the trick: USE database_name; GO BACKUP DATABASE database_name TO DISK = ‘D:\DatabaseBackups\database_name.bak’ WITH FORMAT, MEDIANAME = ‘D_Backups’, NAME = ‘Full Backup of database_name‘; GO