Changing the Login Mode Between SQL Auth and Windows? Easy to query

Easy to do:

Change to Windows Authentication

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1

Change to SQL Authentication
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

Which are the top ten most expensive queries? SQL Server.

Easy to do:

select top 10 rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ) as row_no

,       (rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ))%2 as l1

,       creation_time

,       last_execution_time

,       (total_worker_time+0.0)/1000 as total_worker_time

,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]

,       total_logical_reads as [LogicalReads]

,       total_logical_writes as [LogicalWrites]

,       execution_count

,       total_logical_reads+total_logical_writes as [AggIO]

,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]

,       case when sql_handle IS NULL

                then ' '

                else ( substring(st.text,(qs.statement_start_offset+2)/2,       (case when qs.statement_end_offset = -1         then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )

        end as query_text

,       db_name(st.dbid) as db_name

,       st.objectid as object_id

from sys.dm_exec_query_stats  qs

cross apply sys.dm_exec_sql_text(sql_handle) st

where total_worker_time  > 0

order by [AvgCPUTime] desc

Results:

Log information from a single query? Easy.

Easy to do:

SELECT

CAST(CASE when s.growth=0 THEN (CASE WHEN s.type = 2 THEN 0 ELSE 99 END) ELSE s.is_percent_growth END AS int) AS [GrowthType],

s.name AS [Name],

s.file_id AS [ID],

s.physical_name AS [FileName],

s.size * CONVERT(float,8) AS [Size],

CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],

CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],

s.is_media_read_only AS [IsReadOnlyMedia],

s.is_read_only AS [IsReadOnly],

CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],

s.is_sparse AS [IsSparse]

FROM

sys.master_files AS s

WHERE

(s.name='defa_log')

Results:

GrowthType	Name	ID	FileName	Size	MaxSize	Growth	IsReadOnlyMedia	IsReadOnly	IsOffline	IsSparse
1	defa_log	2	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\defa_log.ldf	1024	2.147E+09	10	0	0	0	0

A File Exists from SQL Server Query?

Easy to do:

exec xp_fileexist 'c:\renepally.txt'

Results:

File Exists	1
File is a Directory	0
Parent Directory Exists	1

Want to get the current Platform in which SQL is running? Easy to do

Easy to do:

create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))

                        insert #SVer exec master.dbo.xp_msver

SELECT (select Value from #SVer where Name = N'Platform') AS [Platform]

                        drop table #SVer

Results:
Platform
NT INTEL X86

Server features and some server properties?

Easy to do:

SELECT

CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],

CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],

@@MAX_PRECISION AS [MaxPrecision],

SERVERPROPERTY(N'ProductVersion') AS [VersionString],

CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],

CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],

CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],

CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],

convert(sysname, serverproperty(N'collation')) AS [Collation],

CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],

CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],

SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],

SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],

SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],

SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],

SERVERPROPERTY(N'CollationID') AS [CollationID],

SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],

SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],

SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],

SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],

SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName]

Results:

IsFullTextInstalled	1
IsCaseSensitive	0
MaxPrecision	38
VersionString	10.0.4000.0
Edition	Standard Edition
ProductLevel	SP2
IsSingleUser	0
EngineEdition	2
Collation	SQL_Latin1_General_CP1_CI_AS
IsClustered	0
NetName	MYSERVER
BuildClrVersionString	v2.0.50727
ComputerNamePhysicalNetBIOS	MYSERVER
ResourceVersionString	10.00.4000
ResourceLastUpdateDateTime	11:59.2
CollationID	872468488
ComparisonStyle	196609
SqlCharSet	1
SqlCharSetName	iso_1
SqlSortOrder	52
SqlSortOrderName	nocase_iso

Which is the SQL Server Install Path?

Easy to do. This tutorial shows how to read registry keys also:

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
select @SmoRoot

Results:
c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL

Rename a database with a query? Easy

Easy:

ALTER DATABASE [perro] MODIFY NAME = [gato]

Results:

Change the database name from perro to gato.

Get extended properties of a table and columns? Easy.

Want to get the DESCRIPTION and other extended features of the table GATO1? easy:

select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int), xp.minor_id, COL_NAME(xp.major_id, xp.minor_id)  from sys.extended_properties xp  where xp.class = 1 and  xp.minor_id > 0 and xp.major_id = object_id(N'dbo.GATO1')  and xp.name in (N'MS_Description')  order by xp.minor_id

Results:

Adding extended properties to the table on SQL Server? (e.g. Description)

Easy to do:

use gato

DECLARE @v sql_variant

SET @v = N'DES'

EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'GATO1', N'COLUMN', N'ID'

Results:

Add description to the TABLE GATO1 on ID column. The description is DES

Change the password of the account? easy.

Easy to do (Changing the password of my account to Bolivia):

ALTER LOGIN [demo] WITH PASSWORD=N'bolivia'

Enable or disable an account using a query? Easy.

Easy to do:

/*Enable account*/

ALTER LOGIN [demo90] ENABLE

/*Disable account*/

ALTER LOGIN [demo90] DISABLE

Do you want to delete a DB with the backup history? Easy (SQL Server)

Easy to do:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'FIN63_JAP'
DROP DATABASE [FIN63_JAP]

Results:

Deletes the database including backup information.

Do you want to detect is Full Text is enabled on SQL Server for your DB? Just a query

Easy to do:

SELECT

dtb.is_fulltext_enabled AS [IsFullTextEnabled]

FROM

master.sys.databases AS dtb

WHERE

(dtb.name='dml_sql_u_man')

The result of this query will give us a result if SQL Server has enabled full text

Do you want to get a complete information of the columns of a table on SQL Server? Just a query

Easy to do:

select col.name, col.column_id, st.name as DT_name, schema_name(st.schema_id) as DT_schema, col.max_length, col.precision, col.scale, bt.name as BT_name, col.collation_name, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol, col.is_identity, case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.seed_value) end, case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.increment_value) end, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as IsIdNotForRepl, col.is_replicated, col.is_non_sql_subscribed, col.is_merge_published, col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name, schema_name(robj.schema_id) as Rul_schema, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name, schema_name(dobj.schema_id) as def_schema, CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_FullTextCol, col_name(col.object_id, ftc.type_column_id) FT_type_column, ftc.language_id as FT_language_id, case when(cmc.column_id is null) then null else cmc.definition end as formular, case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted, defCst.definition, COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic, xmlcoll.name as xmlSchema_name, schema_name(xmlcoll.schema_id) as xmlSchema_schema, col.is_xml_document, col.is_sparse, col.is_column_set from sys.columns col left outer join sys.types st on st.user_type_id = col.user_type_id left outer join sys.types bt on bt.user_type_id = col.system_type_id left outer join sys.objects robj on robj.object_id = col.rule_object_id and robj.type = 'R' left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id and defCst.parent_column_id = col.column_id left outer join sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and ftc.column_id = col.column_id left outer join sys.xml_schema_collections xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where col.object_id = object_id(N'dbo.PRODUCT_WAREHOUSE') order by col.column_id

The result of this query will give us the list of columns and their properties for our SQL Server table: