Databases

To create a tablespace in Oracle, use below query. create tablespace <tableSpaceName> datafile '<datafileLocation>' size 50m autoextend on; Without autoextend on option, the data file is not growing when the table/db growing big.

To view all foreign key in a MySQL database, use below query. select table_name, column_name, constraint_name, referenced_table_name, referenced_column_name from information_schema.key_column_usage where referenced_table_schema = '<databaseName>';

To create a table in In-memory, follow below steps: Create a database create database test; You must set to AUTO_CLOSE OFF because 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA file group. alter database test set auto_close off; The next step would be creating a required file group for memory-optimized tables. To do that, we need to add a memory-optimized file group to test. alter database test add filegroup test_mem contains MEMORY_OPTIMIZED_DATA; Notice the key word MEMORY_OPTIMIZED_DATA, it tells SQL server this file group is in memory and will store memory-optimized objects. Now let's add a file into this file group. alter database test add file(name = 'fileName', filename = 'fileLocation') to filegroup test_mem; Once the memory - optimized data file is ready, we can go ahead and create our first memory - optimized table. use test; create table test_table(id int not null primary key nonclustered hash with (bucket...

Find all the foreign keys by executing below query. SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id; To find/drop a foreign key, use below query. declare @table_name nvarchar(256) declare @col_name nvarchar(256) declare @Command nvarchar(1000) set @table_name = N'<tableName>' set @col_name = N'<columnName>' select @command = 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' drop constraint ' + f.name from sys.foreign_keys f, sys.foreign_key_columns fc where f.object_id = fc.constraint_object_id and OBJECT_NAME(f.parent_object_id) = @table_name and COL_NAME(fc.parent_object_id, fc.parent_column_id) =...

To list the users with their rights, use below query. You have to system admin (sysadmin) rights to execute this query. SELECT name ,type_desc,is_disabled, create_date FROM master.sys.server_principals WHERE IS_SRVROLEMEMBER ( 'sysadmin' , name ) = 1 ORDER BY name;

To execute queries from a SQL file, use below query. MySQL: There are 2 options: If you are in MySQL command line, execute below query. source 'SQLfilePath'; If you are in command line/shell, execute below query. mysql -u userName -p password database < 'SQLfilePath' MSSQL: Use T-SQL, execute below query. sqlcmd -S databaseName -i "SQLfilePath" -o "outputFilePath"

SQL Server offer 3 recovery models Simple Bulk-logged Full In the case of a simple recovery model, you are essentially agreeing that it is ok to lose all changes made to your database after the last full backup . The upside of this recovery model is of course that it takes very little administration. You don’t have to worry about anything like a log chain or tons of log backups , or even when the log will be truncated . Obviously this will never be good enough for mission critical databases, but it definitely has its place and its uses. The purpose of the bulk logged recovery model is to allow you perform bulk operations without writing each transaction to the transaction log , and as such improve the performance of your bulk operation . The downside to this is of course that not each transaction is recorded in the log, and as such i t does not allow you to do a point in time restore. In order to be able to restore to a specific point in time , you need to have your da...

Point in time restore requires  a full log chain. This means  you need to restore that last full backup and all log backups including the one which contains the point you would like to restore to. RESTORE DATABASE <databaseName> FROM DISK = 'E:\Backup\test.bak' WITH FILE = 2 ,REPLACE ,NORECOVERY; From the  above, we will restore the full backup from the file ' E:\Backup\test.bak'.   Since this file contains multiple backups, we need to specify which FILE to use. You can identify the file number by looking at the Position column when executing this command: RESTORE HEADERONLY FROM DISK = 'E:\Backup\test.bak'; Restore each log, specifying the date and time to STOP AT. This needs to be specified in each RESTORE LOG statement RESTORE LOG <databaseName> FROM DISK = ' E:\Backup\test.bak' WITH FILE = 4 , NORECOVERY , STOPAT = '2013-09-28 10:16:28.873’; And finally recover the database RESTORE DATABASE < databaseName> WITH ...

Restore database to existing MSSQL database To restore database to existing active MSSQL database, use below query.  restore database <databaseName > from disk = ' < backupFileName > ' with replace;

Drop partitions without affect the status of the indexes   To drop partitions without affect the index status, use below query. ALTER   TABLE   <tableName>   DROP   PARTITION <partitionName>   UPDATE   GLOBAL   INDEXES;

Select/drop records by partitions   To select records in a table using partitions, use below query. SELECT * FROM <tableName> PARTITION (<partitionName>); To drop records in a table using partitions, use below query. ALTER   TABLE   <tableName>   DROP   PARTITION <partitionName>;

Table and its count To view all tables and its count by executing command and immediately, use below commands - MSSQL SELECT      sc. name   + '.' + ta. name   TableName,  SUM (pa. rows ) RowCnt FROM      sys.tables ta INNER   JOIN   sys.partitions pa      ON   pa.OBJECT_ID = ta.OBJECT_ID INNER   JOIN   sys.schemas sc      ON   ta.schema_id = sc.schema_id WHERE   ta.is_ms_shipped = 0  AND   pa.index_id  IN   (1,0) GROUP   BY   sc. name ,ta. name ORDER   BY   SUM (pa. rows )  DESC  

Export and import records in a table using BCP To export records in a table, using below command. bcp [databaseName].dbo.tableName out <backupLocation> -n -T To import records to a table, using below command. bcp [ databaseName ].dbo. tableName IN < backupLocation> -b 5000 -h  "TABLOCK"   -m 1 -n -e <errorLogLocation> -o <outputLogLocation> -S -T