/* This code clears and reloads the PCS_PRODUCT_ATTRIBUTE table with the prod as first column
followed by the field name and field value for each name in a given table.
it outputs this information in item_key order followed by attributes in the order they
are defined in the table based on the ordinal position of the field.
For example, if icsp were used, the fields would be cono, lookupnm, prodcat, transdt,
transtm, etc.
The table will be loaded with all items and cono first, then all items and lookupnm, then
all items and prodcat, etc.
It is coded not to put out a line if a field is null, zero, or blank'
Current there is a TOP 20 in the select query for going through icsp without hitting
every item.
*/

use sximage

Declare @TN as varchar(200), @CN as varchar(200), @DT as varchar(30), @OP as int,
@item_key varchar(30), @SQL as nvarchar(1000), @SN as varchar(200), @Exact_Match bit

-- this field name will be bypassed and printed as the actual ITEM_KEY in the table
set @item_key = 'prod'  

delete pcs.dbo.PCS_PRODUCT_ATTRIBUTE
 
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name, C.Data_Type, C.Ordinal_Position, T.Table_Schema
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C 
On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
-- Change this table name to a specific table or like 'ATT_%' if all tables
-- for attributes start with the same characters.
Where T.Table_Name = 'icsp_live' and C.Column_Name<>@item_key 
order by 1, 4
-- and C.Data_Type not in ('int','decimal')
Open myCursor
Fetch Next From myCursor Into @TN, @CN, @DT, @OP, @SN
While @@Fetch_Status <> -1
Begin
    Set @SQL = N'Insert into pcs.dbo.PCS_PRODUCT_ATTRIBUTE 
					Select top 20 [' + @item_key + '] as ITEM_KEY, ''' + @CN  + ''' as ATTRIBUTE_KEY, 
						[' + @CN + '] as ATTRIBUTE_VALUE,
						NULL as CATEGORY_KEY, NULL as ATTRIBUTE_LOOKUP_KEY,
						NULL as FLAG_PRIMARY, NULL as DIVISION_KEY
					From [' + @SN + '].[' + @TN + '] 
				Where [' + @CN  + '] <> NULL 
					AND ( ''' + @DT + ''' not in (''' + 'int' + ''',''' + 'decimal' + ''') 
					AND LTRIM(RTRIM([' + @CN + '])) <> ''' + '''
					OR ''' + @DT + ''' in (''' + 'int' + ''',''' + 'decimal' + ''') 
					AND [' + @CN + '] <> 0 )
				/* ''' + @item_key + '''*/'
        --Print @SQL
        Exec sp_executesql @SQL 
        Fetch Next From myCursor Into @TN, @CN, @DT, @OP, @SN
End
Close myCursor
Deallocate myCursor

// description of your code here

EXEC sp_helpdb yourDataBase
EXEC sp_helpfile 
EXEC  sp_spaceused yourTable

-- This SQL code allows you to join a table to another table by finding columns that contain another column's information
-- (instead of where the columns are equal)

SELECT *
FROM Table1
INNER JOIN Table2 ON Table1.col LIKE '%' + Table2.col + '%'

--Extracts the date from the date time

CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)

// Reindex and rebuild statistics for each table in the database

EXEC sp_MSforeachtable "dbcc dbreindex('?')"
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Thanks to CodeProject for this code

Run this in the Query Analyzer (make sure the right database is selected first).

sp_change_users_login 'auto_fix', 'UserName'

Thanks to this website for the information:

http://www.databasejournal.com/features/mssql/article.php/1442021

-- The syntax for pulling a certain part of a DATETIME is:
--
-- CONVERT(date_type[(length)],expression[,style])
--
-- The available styles are as follows:
--
-- NULL  Jun 24 2001 9:48PM
-- 1     06/24/01
-- 101   06/24/2001
-- 2     01.06.24
-- 104   24.06.2001
-- 108   21:48:00
-- 112   20010624
-- 121   2001-06-24 21:48:00.000

-- Some example usage:
SELECT CONVERT(DATETIME,GETDATE(),112) as date
-- Will output in YYYYMMDD format
SELECT CONVERT(DATETIME,client.birthday,101) as birthday
-- Will output in MM/DD/YYYY format

Props to this site for the answer:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5

// MySQL limit clause
SELECT emp_id,lname,fname FROM employee LIMIT 20,10

// MSSQL equivelent
select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname
    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc

"The main thing to remember is to deal with all your ORDER BY clauses and also to use the inner TOP as the sum of what you are looking for. In this example - 30 since you start at 20 and want 10 rows (20+10=30)."
_

This SQL statement will alter a table only if a column in the table to be altered does NOT contain a column named 'FavoriteColorId'.

From: http://haacked.com/

IF NOT EXISTS
(
    SELECT * FROM [information_schema].[columns]
    WHERE    table_name   = 'Customer'
    AND      table_schema = 'dbo'
    AND      column_name  = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS)SQL (all I've tested it in).

SELECT * FROM information_schema.columns WHERE table_name = 'Customers'