TODO:
Have you ever wanted to backup your database master key and move it to a new server?
SOLUTION:
--Step 1 -- Backup file
OPEN MASTER KEY DECRYPTION BY PASSWORD 'my password'
BACKUP MASTER KEY TO FILE = 'c:\myfile' ENCRYPTION BY PASSWORD ='mypassword'
--Step 2 -- Restore file
USE myDatabase
GO
RESTORE MASTER KEY FROM FILE 'my file'
DECRYPTION BY PASSWORD = 'mypassword'
ENCRYPTION BY PASSWORD = 'mypassword'
FORCE;
GO
NOTES:
You may not need the 'FORCE' option, read up on what it does and decide accordingly.
TODO:
Have you ever wanted to list all columns in all User tables that exist in a SQL Server Database?
SOLUTION:
SELECT so.name "table", sc.name "column", sm.text "Default"
FROM dbo.sysobjects so INNER JOIN dbo.syscolumns sc ON so.id = sc.id
LEFT JOIN dbo.syscomments sm ON sc.cdefault = sm.id
WHERE so.xtype = 'U'
ORDER BY so.[name], sc.colid
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to have a table of US states and not have to type them in. If so, run the SQL below. It assumes you have a table named 'State', with fields of StateId (identity), Abbreviation, and Name.
SOLUTION:
insert into state(abbreviation,name) values('AL','Alabama');
insert into state(abbreviation,name) values('AK','Alaska');
insert into state(abbreviation,name) values('AS','American Somoa');
insert into state(abbreviation,name) values('AZ','Arizona');
insert into state(abbreviation,name) values('AR','Arkansas');
insert into state(abbreviation,name) values('AE','Armed Forces Africa, Canada, Middle East, Europe');
insert into state(abbreviation,name) values('AA','Armed Forces America (except Canada)');
insert into state(abbreviation,name) values('AP','Armed Forces Pacific');
insert into state(abbreviation,name) values('CA','California');
insert into state(abbreviation,name) values('CO','Colorado');
insert into state(abbreviation,name) values('CT','Connecticut');
insert into state(abbreviation,name) values('DE','Delaware');
insert into state(abbreviation,name) values('DC','District of Columbia');
insert into state(abbreviation,name) values('FM','Federated States of Micronesia');
insert into state(abbreviation,name) values('FL','Florida');
insert into state(abbreviation,name) values('GA','Georgia');
insert into state(abbreviation,name) values('GU','Guam');
insert into state(abbreviation,name) values('HI','Hawaii');
insert into state(abbreviation,name) values('ID','Idaho');
insert into state(abbreviation,name) values('IL','Illinois');
insert into state(abbreviation,name) values('IN','Indiana');
insert into state(abbreviation,name) values('IA','Iowa');
insert into state(abbreviation,name) values('KS','Kansas');
insert into state(abbreviation,name) values('KY','Kentucky');
insert into state(abbreviation,name) values('LA','Louisiana');
insert into state(abbreviation,name) values('ME','Maine');
insert into state(abbreviation,name) values('MH','Marshall Islands');
insert into state(abbreviation,name) values('MD','Maryland');
insert into state(abbreviation,name) values('MA','Massachusetts');
insert into state(abbreviation,name) values('MI','Michigan');
insert into state(abbreviation,name) values('MN','Minnesota');
insert into state(abbreviation,name) values('MS','Mississippi');
insert into state(abbreviation,name) values('MO','Missouri');
insert into state(abbreviation,name) values('MT','Montana');
insert into state(abbreviation,name) values('NE','Nebraska');
insert into state(abbreviation,name) values('NV','Nevada');
insert into state(abbreviation,name) values('NH','New Hampshire');
insert into state(abbreviation,name) values('NJ','New Jersey');
insert into state(abbreviation,name) values('NM','New Mexico');
insert into state(abbreviation,name) values('NY','New York');
insert into state(abbreviation,name) values('NC','North Carolina');
insert into state(abbreviation,name) values('ND','North Dakota');
insert into state(abbreviation,name) values('MP','Northern Mariana Islands');
insert into state(abbreviation,name) values('OH','Ohio');
insert into state(abbreviation,name) values('OK','Oklahoma');
insert into state(abbreviation,name) values('OR','Oregon');
insert into state(abbreviation,name) values('PM','Palau');
insert into state(abbreviation,name) values('PA','Pennsylvania');
insert into state(abbreviation,name) values('PR','Puerto Rico');
insert into state(abbreviation,name) values('RI','Rhode Island');
insert into state(abbreviation,name) values('SC','South Carolina');
insert into state(abbreviation,name) values('SD','South Dakota');
insert into state(abbreviation,name) values('TN','Tennessee');
insert into state(abbreviation,name) values('TX','Texas');
insert into state(abbreviation,name) values('VI','U.S. Virgin Islands');
insert into state(abbreviation,name) values('UT','Utah');
insert into state(abbreviation,name) values('VT','Vermont');
insert into state(abbreviation,name) values('VA','Virginia');
insert into state(abbreviation,name) values('WA','Washington');
insert into state(abbreviation,name) values('WV','West Virginia');
insert into state(abbreviation,name) values('WI','Wisconsin');
insert into state(abbreviation,name) values('WY','Wyoming');
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to get a list of all of the columns in a database table, and their details, ordered by name?
SOLUTION:
SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,ISNULL(CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH),'')
,IS_NULLABLE
,ISNULL(COLUMN_DEFAULT,'')
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'tbl_name_here'
ORDER BY
COLUMN_NAME ASC;
NOTES:
No notes on this topic.