Database Development

Rebuild All Database Indexes

You can rebuild all indexes present in your database with the following query: EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)” GO EXEC sp_updatestats GO

Preventing SQL from Running on a Testing SQL Server

There are two ways to prevent your SQL code from running on a testing or production server. You can use the @@SERVERNAME Variable or the DB_NAME function, as shown next below: @@SERVERNAMEIF @@SERVERNAME = ‘ProductionServerName’BEGINPRINT ‘Code will NOT execute’RETURNENDELSEBEGINPRINT ‘Code will execute’ENDDB_NAME()IF DB_NAME() = ‘ProductionServerName’BEGINPRINT ‘Code will NOT execute’RETURNENDELSEBEGINPRINT ‘Code

Knowing the Sign of a Given Number in MySQL

For a given number, it may be necessary to know the sign of the number for computation purposes. If it is positive, zero or negative, you may have respective case to work on. The signs are returned as 1, 0, -1 for positive number, zero and negative number respectively. Case

Know if LIKE Results are True or False

MySQL is capable of returning the result of a LIKE directly in the result set itself. For example, if you use a wildcard character in a query such as below, you get to know if the result is true or false based on the value of the resulting column. If

Find Tables Referenced by Foreign Keys

You can easily find tables referenced by foreign keys with a script similar to the following: SELECT = s.name + N’.’ + t.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.foreign_keys AS fk WHERE fk.referenced_object_id = t.[object_id] );

Getting Information About the Structure of Tables in SQL

Sp_help?provides a lot of information about the structure of the tables inside your database. You can highlight a table name in the SQL Query editor and then press the Alt + F1 key combination or you could type a command such as the following: sp_help ‘TableName’ This will produce a

Understanding Usage of the Position Function

POSITION is a powerful function that helps you to find the position of a sub string in a string. Typically, the string is a column in a given table and we use the POSITION function to find the sub string. Example: SELECT POSITION(” IN ) FROM ; This returns a

No more posts to show