SQL Queries

1. To get version of SQL server and operating system?

• SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

2. How to find tables without Indexes?

• SELECT SCHEMA_NAME(schema_id) AS schema_name,name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,‘IsIndexed’) = 0ORDER BY schema_name, table_name;

3. Searching text in body of db objects:

• select distinct object_name(id) from syscomments where text like '%Test To Searched%'

4. Updates in Joins, or updating table based on data.

• Update orderline set ord_date=y.Minorddate from orderline x (nolock), (select ord_invNo,min(ord_date) minorddate from orderline (nolock) where cust_id = 1000 group by ord_invNo ) y where x.cust_id = 1000 and x.ord_invNo = y.ord_invNo

5. This query will return a listing of all files in all databases on a SQL instance:

• EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
  DBName E:\DataBase\DBName.mdf 288976DBName E:\Database\DBName.ldf 48568