Aliasing Column Names

Today, I discovered a new way columns can be aliased.

select o.*

from

(

      select id, name, xtype

      from sys.sysobjects

) o

join sys.syscomments c on o.id = c.id

select o.*

from

(

      select id as a, name as b, xtype as c

      from sys.sysobjects

) o

join sys.syscomments c on o.a = c.id

select o.*

from

(

      select id, name, xtype

      from sys.sysobjects

) o (a, b, c)

join sys.syscomments c on o.a = c.id

-- XP_CMDSHELL

-- Tab keyed in

EXEC xp_cmdshell 'sqlcmd -U user -P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"     " -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~-11,2%%time:~-8,2%%time:~-5,2%.xls"'

-- <TAB>

EXEC xp_cmdshell 'sqlcmd -U user -P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"<TAB>" -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~-11,2%%time:~-8,2%%time:~-5,2%.xls"'

-- COMMAND LINE - CMD.EXE

sqlcmd -U user -P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"<TAB>" -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~-11,2%%time:~-8,2%%time:~-5,2%.xls"

-- SQL AGENT - USE OF "SQL AGENT TOKENS" BECAUSE SQL AGENT DOES NOT RECOGNIZE WINDOWS DATE, TIME

sqlcmd -U user-P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"<TAB>" -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_$(ESCAPE_NONE(DATE)) _$(ESCAPE_NONE(TIME)).xls"

-- DATE TIME WINDOWS FORMATS

%date:~x,y%

where x is starting character position of date substring,

and y is ending character position of date substring

"-" determines whether it counts from left-to-right or right-to-left in the date string.

-- DATE TIME SQL AGENT TOKENS

(DATE) Current date (in YYYYMMDD format).

(TIME) Current time (in HHMMSS format).

$(ESCAPE_NONE( token_name )) Replaces token without escaping any characters in the string. This macro is provided to support backward compatibility in environments where token replacement strings are only expected from trusted users.

http://msdn.microsoft.com/en-us/library/ms175575.aspx

Remove compression off a table or index

-- TABLES

SELECT distinct st.name, st.object_id, schema_name(schema_id), 'ALTER TABLE ' + schema_name(schema_id) + '.' + st.name + ' REBUILD WITH (DATA_COMPRESSION =  NONE)'--, sp.partition_id, sp.partition_number, sp.data_compression, sp.data_compression_desc

FROM sys.partitions SP

INNER JOIN sys.tables ST ON st.object_id = sp.object_id

WHERE data_compression <> 0

and index_id = 1

-- INDEXES -- DROP AND CREATE INDEX WITH "DATA_COMPRESSION = NONE"

SELECT distinct st.name, st.object_id, schema_name(schema_id), i.name

FROM sys.partitions SP

INNER JOIN sys.tables ST ON st.object_id = sp.object_id

join sys.sysindexes i on SP.object_id = i.id and SP.index_id = i.indid

WHERE data_compression <> 0

and index_id <> 1