How To Populate A Table With US State Data



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.

How To Populate A Table With ISO 3166-1 Country Data



TODO:

Did you ever need a reference table of ISO country data?  Is so, create a table called 'Country', and add an identity column named 'CountryId', IsoCode, and Name, then execute this SQL. 

 

SOLUTION:

 

insert into country(IsoCode, Name) Values ('AF','AFGHANISTAN');
insert into country(IsoCode, Name) Values ('AX','åLAND ISLANDS');
insert into country(IsoCode, Name) Values ('AL','ALBANIA');
insert into country(IsoCode, Name) Values ('DZ','ALGERIA');
insert into country(IsoCode, Name) Values ('AS','AMERICAN SAMOA');
insert into country(IsoCode, Name) Values ('AD','ANDORRA');
insert into country(IsoCode, Name) Values ('AO','ANGOLA');
insert into country(IsoCode, Name) Values ('AI','ANGUILLA');
insert into country(IsoCode, Name) Values ('AQ','ANTARCTICA');
insert into country(IsoCode, Name) Values ('AG','ANTIGUA AND BARBUDA');
insert into country(IsoCode, Name) Values ('AR','ARGENTINA');
insert into country(IsoCode, Name) Values ('AM','ARMENIA');
insert into country(IsoCode, Name) Values ('AW','ARUBA');
insert into country(IsoCode, Name) Values ('AU','AUSTRALIA');
insert into country(IsoCode, Name) Values ('AT','AUSTRIA');
insert into country(IsoCode, Name) Values ('AZ','AZERBAIJAN');
insert into country(IsoCode, Name) Values ('BS','BAHAMAS');
insert into country(IsoCode, Name) Values ('BH','BAHRAIN');
insert into country(IsoCode, Name) Values ('BD','BANGLADESH');
insert into country(IsoCode, Name) Values ('BB','BARBADOS');
insert into country(IsoCode, Name) Values ('BY','BELARUS');
insert into country(IsoCode, Name) Values ('BE','BELGIUM');
insert into country(IsoCode, Name) Values ('BZ','BELIZE');
insert into country(IsoCode, Name) Values ('BJ','BENIN');
insert into country(IsoCode, Name) Values ('BM','BERMUDA');
insert into country(IsoCode, Name) Values ('BT','BHUTAN');
insert into country(IsoCode, Name) Values ('BO','BOLIVIA');
insert into country(IsoCode, Name) Values ('BQ','BONAIRE');
insert into country(IsoCode, Name) Values ('BA','BOSNIA AND HERZEGOVINA');
insert into country(IsoCode, Name) Values ('BW','BOTSWANA');
insert into country(IsoCode, Name) Values ('BV','BOUVET ISLAND');
insert into country(IsoCode, Name) Values ('BR','BRAZIL');
insert into country(IsoCode, Name) Values ('IO','BRITISH INDIAN OCEAN TERRITORY');
insert into country(IsoCode, Name) Values ('BN','BRUNEI DARUSSALAM');
insert into country(IsoCode, Name) Values ('BG','BULGARIA');
insert into country(IsoCode, Name) Values ('BF','BURKINA FASO');
insert into country(IsoCode, Name) Values ('BI','BURUNDI');
insert into country(IsoCode, Name) Values ('KH','CAMBODIA');
insert into country(IsoCode, Name) Values ('CM','CAMEROON');
insert into country(IsoCode, Name) Values ('CA','CANADA');
insert into country(IsoCode, Name) Values ('CV','CAPE VERDE');
insert into country(IsoCode, Name) Values ('KY','CAYMAN ISLANDS');
insert into country(IsoCode, Name) Values ('CF','CENTRAL AFRICAN REPUBLIC');
insert into country(IsoCode, Name) Values ('TD','CHAD');
insert into country(IsoCode, Name) Values ('CL','CHILE');
insert into country(IsoCode, Name) Values ('CN','CHINA');
insert into country(IsoCode, Name) Values ('CX','CHRISTMAS ISLAND');
insert into country(IsoCode, Name) Values ('CC','COCOS (KEELING) ISLANDS');
insert into country(IsoCode, Name) Values ('CO','COLOMBIA');
insert into country(IsoCode, Name) Values ('KM','COMOROS');
insert into country(IsoCode, Name) Values ('CG','CONGO');
insert into country(IsoCode, Name) Values ('CD','THE DEMOCRATIC REPUBLIC OF THE CONGO');
insert into country(IsoCode, Name) Values ('CK','COOK ISLANDS');
insert into country(IsoCode, Name) Values ('CR','COSTA RICA');
insert into country(IsoCode, Name) Values ('CI','CôTE D''IVOIRE');
insert into country(IsoCode, Name) Values ('HR','CROATIA');
insert into country(IsoCode, Name) Values ('CU','CUBA');
insert into country(IsoCode, Name) Values ('CW','CURAçAO');
insert into country(IsoCode, Name) Values ('CY','CYPRUS');
insert into country(IsoCode, Name) Values ('CZ','CZECH REPUBLIC');
insert into country(IsoCode, Name) Values ('DK','DENMARK');
insert into country(IsoCode, Name) Values ('DJ','DJIBOUTI');
insert into country(IsoCode, Name) Values ('DM','DOMINICA');
insert into country(IsoCode, Name) Values ('DO','DOMINICAN REPUBLIC');
insert into country(IsoCode, Name) Values ('EC','ECUADOR');
insert into country(IsoCode, Name) Values ('EG','EGYPT');
insert into country(IsoCode, Name) Values ('SV','EL SALVADOR');
insert into country(IsoCode, Name) Values ('GQ','EQUATORIAL GUINEA');
insert into country(IsoCode, Name) Values ('ER','ERITREA');
insert into country(IsoCode, Name) Values ('EE','ESTONIA');
insert into country(IsoCode, Name) Values ('ET','ETHIOPIA');
insert into country(IsoCode, Name) Values ('FK','FALKLAND ISLANDS (MALVINAS)');
insert into country(IsoCode, Name) Values ('FO','FAROE ISLANDS');
insert into country(IsoCode, Name) Values ('FJ','FIJI');
insert into country(IsoCode, Name) Values ('FI','FINLAND');
insert into country(IsoCode, Name) Values ('FR','FRANCE');
insert into country(IsoCode, Name) Values ('GF','FRENCH GUIANA');
insert into country(IsoCode, Name) Values ('PF','FRENCH POLYNESIA');
insert into country(IsoCode, Name) Values ('TF','FRENCH SOUTHERN TERRITORIES');
insert into country(IsoCode, Name) Values ('GA','GABON');
insert into country(IsoCode, Name) Values ('GM','GAMBIA');
insert into country(IsoCode, Name) Values ('GE','GEORGIA');
insert into country(IsoCode, Name) Values ('DE','GERMANY');
insert into country(IsoCode, Name) Values ('GH','GHANA');
insert into country(IsoCode, Name) Values ('GI','GIBRALTAR');
insert into country(IsoCode, Name) Values ('GR','GREECE');
insert into country(IsoCode, Name) Values ('GL','GREENLAND');
insert into country(IsoCode, Name) Values ('GD','GRENADA');
insert into country(IsoCode, Name) Values ('GP','GUADELOUPE');
insert into country(IsoCode, Name) Values ('GU','GUAM');
insert into country(IsoCode, Name) Values ('GT','GUATEMALA');
insert into country(IsoCode, Name) Values ('GG','GUERNSEY');
insert into country(IsoCode, Name) Values ('GN','GUINEA');
insert into country(IsoCode, Name) Values ('GW','GUINEA-BISSAU');
insert into country(IsoCode, Name) Values ('GY','GUYANA');
insert into country(IsoCode, Name) Values ('HT','HAITI');
insert into country(IsoCode, Name) Values ('HM','HEARD ISLAND AND MCDONALD ISLANDS');
insert into country(IsoCode, Name) Values ('VA','HOLY SEE (VATICAN CITY STATE)');
insert into country(IsoCode, Name) Values ('HN','HONDURAS');
insert into country(IsoCode, Name) Values ('HK','HONG KONG');
insert into country(IsoCode, Name) Values ('HU','HUNGARY');
insert into country(IsoCode, Name) Values ('IS','ICELAND');
insert into country(IsoCode, Name) Values ('IN','INDIA');
insert into country(IsoCode, Name) Values ('ID','INDONESIA');
insert into country(IsoCode, Name) Values ('IR','IRAN');
insert into country(IsoCode, Name) Values ('IQ','IRAQ');
insert into country(IsoCode, Name) Values ('IE','IRELAND');
insert into country(IsoCode, Name) Values ('IM','ISLE OF MAN');
insert into country(IsoCode, Name) Values ('IL','ISRAEL');
insert into country(IsoCode, Name) Values ('IT','ITALY');
insert into country(IsoCode, Name) Values ('JM','JAMAICA');
insert into country(IsoCode, Name) Values ('JP','JAPAN');
insert into country(IsoCode, Name) Values ('JE','JERSEY');
insert into country(IsoCode, Name) Values ('JO','JORDAN');
insert into country(IsoCode, Name) Values ('KZ','KAZAKHSTAN');
insert into country(IsoCode, Name) Values ('KE','KENYA');
insert into country(IsoCode, Name) Values ('KI','KIRIBATI');
insert into country(IsoCode, Name) Values ('KP','DEMOCRATIC PEOPLE''S REPUBLIC OF KOREA');
insert into country(IsoCode, Name) Values ('KR','REPUBLIC OF KOREA');
insert into country(IsoCode, Name) Values ('KW','KUWAIT');
insert into country(IsoCode, Name) Values ('KG','KYRGYZSTAN');
insert into country(IsoCode, Name) Values ('LA','LAO PEOPLE''S DEMOCRATIC REPUBLIC');
insert into country(IsoCode, Name) Values ('LV','LATVIA');
insert into country(IsoCode, Name) Values ('LB','LEBANON');
insert into country(IsoCode, Name) Values ('LS','LESOTHO');
insert into country(IsoCode, Name) Values ('LR','LIBERIA');
insert into country(IsoCode, Name) Values ('LY','LIBYA');
insert into country(IsoCode, Name) Values ('LI','LIECHTENSTEIN');
insert into country(IsoCode, Name) Values ('LT','LITHUANIA');
insert into country(IsoCode, Name) Values ('LU','LUXEMBOURG');
insert into country(IsoCode, Name) Values ('MO','MACAO');
insert into country(IsoCode, Name) Values ('MK','MACEDONIA');
insert into country(IsoCode, Name) Values ('MG','MADAGASCAR');
insert into country(IsoCode, Name) Values ('MW','MALAWI');
insert into country(IsoCode, Name) Values ('MY','MALAYSIA');
insert into country(IsoCode, Name) Values ('MV','MALDIVES');
insert into country(IsoCode, Name) Values ('ML','MALI');
insert into country(IsoCode, Name) Values ('MT','MALTA');
insert into country(IsoCode, Name) Values ('MH','MARSHALL ISLANDS');
insert into country(IsoCode, Name) Values ('MQ','MARTINIQUE');
insert into country(IsoCode, Name) Values ('MR','MAURITANIA');
insert into country(IsoCode, Name) Values ('MU','MAURITIUS');
insert into country(IsoCode, Name) Values ('YT','MAYOTTE');
insert into country(IsoCode, Name) Values ('MX','MEXICO');
insert into country(IsoCode, Name) Values ('FM','MICRONESIA');
insert into country(IsoCode, Name) Values ('MD','MOLDOVA');
insert into country(IsoCode, Name) Values ('MC','MONACO');
insert into country(IsoCode, Name) Values ('MN','MONGOLIA');
insert into country(IsoCode, Name) Values ('ME','MONTENEGRO');
insert into country(IsoCode, Name) Values ('MS','MONTSERRAT');
insert into country(IsoCode, Name) Values ('MA','MOROCCO');
insert into country(IsoCode, Name) Values ('MZ','MOZAMBIQUE');
insert into country(IsoCode, Name) Values ('MM','MYANMAR');
insert into country(IsoCode, Name) Values ('NA','NAMIBIA');
insert into country(IsoCode, Name) Values ('NR','NAURU');
insert into country(IsoCode, Name) Values ('NP','NEPAL');
insert into country(IsoCode, Name) Values ('NL','NETHERLANDS');
insert into country(IsoCode, Name) Values ('NC','NEW CALEDONIA');
insert into country(IsoCode, Name) Values ('NZ','NEW ZEALAND');
insert into country(IsoCode, Name) Values ('NI','NICARAGUA');
insert into country(IsoCode, Name) Values ('NE','NIGER');
insert into country(IsoCode, Name) Values ('NG','NIGERIA');
insert into country(IsoCode, Name) Values ('NU','NIUE');
insert into country(IsoCode, Name) Values ('NF','NORFOLK ISLAND');
insert into country(IsoCode, Name) Values ('MP','NORTHERN MARIANA ISLANDS');
insert into country(IsoCode, Name) Values ('NO','NORWAY');
insert into country(IsoCode, Name) Values ('OM','OMAN');
insert into country(IsoCode, Name) Values ('PK','PAKISTAN');
insert into country(IsoCode, Name) Values ('PW','PALAU');
insert into country(IsoCode, Name) Values ('PS','PALESTINIAN TERRITORY');
insert into country(IsoCode, Name) Values ('PA','PANAMA');
insert into country(IsoCode, Name) Values ('PG','PAPUA NEW GUINEA');
insert into country(IsoCode, Name) Values ('PY','PARAGUAY');
insert into country(IsoCode, Name) Values ('PE','PERU');
insert into country(IsoCode, Name) Values ('PH','PHILIPPINES');
insert into country(IsoCode, Name) Values ('PN','PITCAIRN');
insert into country(IsoCode, Name) Values ('PL','POLAND');
insert into country(IsoCode, Name) Values ('PT','PORTUGAL');
insert into country(IsoCode, Name) Values ('PR','PUERTO RICO');
insert into country(IsoCode, Name) Values ('QA','QATAR');
insert into country(IsoCode, Name) Values ('RE','RéUNION');
insert into country(IsoCode, Name) Values ('RO','ROMANIA');
insert into country(IsoCode, Name) Values ('RU','RUSSIAN FEDERATION');
insert into country(IsoCode, Name) Values ('RW','RWANDA');
insert into country(IsoCode, Name) Values ('BL','SAINT BARTHéLEMY');
insert into country(IsoCode, Name) Values ('SH','SAINT HELENA');
insert into country(IsoCode, Name) Values ('KN','SAINT KITTS AND NEVIS');
insert into country(IsoCode, Name) Values ('LC','SAINT LUCIA');
insert into country(IsoCode, Name) Values ('MF','SAINT MARTIN (FRENCH PART)');
insert into country(IsoCode, Name) Values ('PM','SAINT PIERRE AND MIQUELON');
insert into country(IsoCode, Name) Values ('VC','SAINT VINCENT AND THE GRENADINES');
insert into country(IsoCode, Name) Values ('WS','SAMOA');
insert into country(IsoCode, Name) Values ('SM','SAN MARINO');
insert into country(IsoCode, Name) Values ('ST','SAO TOME AND PRINCIPE');
insert into country(IsoCode, Name) Values ('SA','SAUDI ARABIA');
insert into country(IsoCode, Name) Values ('SN','SENEGAL');
insert into country(IsoCode, Name) Values ('RS','SERBIA');
insert into country(IsoCode, Name) Values ('SC','SEYCHELLES');
insert into country(IsoCode, Name) Values ('SL','SIERRA LEONE');
insert into country(IsoCode, Name) Values ('SG','SINGAPORE');
insert into country(IsoCode, Name) Values ('SX','SINT MAARTEN (DUTCH PART)');
insert into country(IsoCode, Name) Values ('SK','SLOVAKIA');
insert into country(IsoCode, Name) Values ('SI','SLOVENIA');
insert into country(IsoCode, Name) Values ('SB','SOLOMON ISLANDS');
insert into country(IsoCode, Name) Values ('SO','SOMALIA');
insert into country(IsoCode, Name) Values ('ZA','SOUTH AFRICA');
insert into country(IsoCode, Name) Values ('GS','SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS');
insert into country(IsoCode, Name) Values ('SS','SOUTH SUDAN');
insert into country(IsoCode, Name) Values ('ES','SPAIN');
insert into country(IsoCode, Name) Values ('LK','SRI LANKA');
insert into country(IsoCode, Name) Values ('SD','SUDAN');
insert into country(IsoCode, Name) Values ('SR','SURINAME');
insert into country(IsoCode, Name) Values ('SJ','SVALBARD AND JAN MAYEN');
insert into country(IsoCode, Name) Values ('SZ','SWAZILAND');
insert into country(IsoCode, Name) Values ('SE','SWEDEN');
insert into country(IsoCode, Name) Values ('CH','SWITZERLAND');
insert into country(IsoCode, Name) Values ('SY','SYRIAN ARAB REPUBLIC');
insert into country(IsoCode, Name) Values ('TW','TAIWAN');
insert into country(IsoCode, Name) Values ('TJ','TAJIKISTAN');
insert into country(IsoCode, Name) Values ('TZ','TANZANIA');
insert into country(IsoCode, Name) Values ('TH','THAILAND');
insert into country(IsoCode, Name) Values ('TL','TIMOR-LESTE');
insert into country(IsoCode, Name) Values ('TG','TOGO');
insert into country(IsoCode, Name) Values ('TK','TOKELAU');
insert into country(IsoCode, Name) Values ('TO','TONGA');
insert into country(IsoCode, Name) Values ('TT','TRINIDAD AND TOBAGO');
insert into country(IsoCode, Name) Values ('TN','TUNISIA');
insert into country(IsoCode, Name) Values ('TR','TURKEY');
insert into country(IsoCode, Name) Values ('TM','TURKMENISTAN');
insert into country(IsoCode, Name) Values ('TC','TURKS AND CAICOS ISLANDS');
insert into country(IsoCode, Name) Values ('TV','TUVALU');
insert into country(IsoCode, Name) Values ('UG','UGANDA');
insert into country(IsoCode, Name) Values ('UA','UKRAINE');
insert into country(IsoCode, Name) Values ('AE','UNITED ARAB EMIRATES');
insert into country(IsoCode, Name) Values ('GB','UNITED KINGDOM');
insert into country(IsoCode, Name) Values ('US','UNITED STATES');
insert into country(IsoCode, Name) Values ('UM','UNITED STATES MINOR OUTLYING ISLANDS');
insert into country(IsoCode, Name) Values ('UY','URUGUAY');
insert into country(IsoCode, Name) Values ('UZ','UZBEKISTAN');
insert into country(IsoCode, Name) Values ('VU','VANUATU');
insert into country(IsoCode, Name) Values ('VE','VENEZUELA');
insert into country(IsoCode, Name) Values ('VN','VIET NAM');
insert into country(IsoCode, Name) Values ('VG','BRITISH VIRGIN ISLANDS');
insert into country(IsoCode, Name) Values ('VI','U.S. VIRGIN ISLANDS');
insert into country(IsoCode, Name) Values ('WF','WALLIS AND FUTUNA');
insert into country(IsoCode, Name) Values ('EH','WESTERN SAHARA');
insert into country(IsoCode, Name) Values ('YE','YEMEN');
insert into country(IsoCode, Name) Values ('ZM','ZAMBIA');
insert into country(IsoCode, Name) Values ('ZW','ZIMBABWE');

 

NOTES:

This solution assumes you have a table name 'Country', with columns of 'CountryId (identity), Name, and IsoCode

How To Insert Data From A Select



TODO:

Have you ever wanted to insert data by selecting values from a table?


SOLUTION:

Consider we have a table called XMLData, and it has a column called Id, Type, RawData

 

INSERT INTO XMLData (RawType, RawData)
SELECT 'Client', RawData FROM XMLData WHERE RawType = 'Customer'

 

NOTES:

The example above will copy records flagged as RawType = 'Customer', and insert them as new records, changing the RawType to 'Client'

Cannot Copy SQL Server Database Due To "Unable to determine if the owner X of job Y has server access"



TODO:

Have you tried to copy a database in SQL Server Management Studio, and receive the following error "SQL Server Scheduled Job 'CDW_X_X2008_X_X2008_5_4' (0x623E46E604C04B4EB2BA2310660B1E3D) - Status: Failed - Invoked on: 2012-07-12 10:20:47 - Message: The job failed.  Unable to determine if the owner (X\Administrator) of job CDW_X_X2008_X_X2008_5_4 has server access (reason: Could not obtain information about Windows NT group/user 'X\Administrator', error code 0x534. [SQLSTATE 42000] (Error 15404))."

 

SOLUTION:

In my case, the DB owner, was associated with the Administrator account of my old server.  I had replaced it with a new server and moved all of the databases over to the new machine.  I simply went to "Logins" under "Security", and renamed the old user to the new user,  executed sp_changedbowner 'newowner' (use sp_helplogins to get list), and lastly went back and re-ran the copy job, and all worked as expected.

 

NOTES:

There are no notes on this topic.

How To Reseed An Identity In SQL Server Using T-SQL



TODO:

Have you ever wanted to reseed an Identity column in your database?

 

SOLUTION:

DBCC CHECKIDENT (yourtablename, reseed, 1)

 

NOTES:

The above command will reset the identity to start at 1.  You can set that to any value you would like.

How To List All User Tables In SQL Server



TODO:

Have you ever wanted to list all tables that were user created using T-SQL?

 

SOLUTION:

 

SELECT name FROM SYS.OBJECTS WHERE TYPE='U' order by name

 

 

NOTES:

There are no notes on this topic

How To Get Information On SQL Maintenance Plans Job Steps



TODO:

Have you ever wanted to get information on SQL Server Mainenance Plan job steps using T-SQL?

 

SOLUTION:

select * From msdb..sysjobs

 

 

NOTES:

There are no notes on this topic.

How To Find Text That Exists In A Stored Procedure, Function Or Trigger



TODO:

Have you ever wanted to find text that exists in a stored procedure, function or trigger using T-SQL?

 

SOLUTION:

 

SELECT DISTINCT *
FROM sys.sql_modules m 
INNER JOIN sys.objects  o ON m.object_id=o.object_id
WHERE m.definition Like '%enter your search criteria here%'
ORDER BY 2,1

 

 

NOTES:

There are no notes on this topic.

How To Select The Entire Contents Of A Varchar(max) Or NVarchar(max) Column In SQL Server Management Studio



TODO:

Have you ever wanted to select a varchar(max), or nvarchar(max) column in SSMS?  You will notice that the data is truncated when you have a large amount of data stored in the column.  To get around this we will convert the string to XML, then save the results to an XML file, so you can open in your favorite XML editor / viewer.

 

SOLUTION:

 

--to select a column with NON-XML data
select convert(xml,'<xml><![CDATA[' + cast(LogData as varchar(max)) + ']]></xml>') FROM LogTable where Id = 1234

--to select a column with XML data
select convert(xml, LogData) FROM LogTable where Id = 1234

 

 

NOTES:

The first example wraps the data in CDATA tags, so that it will be well-formed XML. 

The second example does not, because I am storing well-formed XML in the column already.

How To Script All Indexes Of A Table Using SQL Server



TODO:

Have you ever wanted to script all indexes for a particular table in SQL Server?

 

SOLUTION:

The solution is simple and requires no code.

  1. Open SQL Server Management Studio
  2. Click "Tools"
  3. Click "Options"
  4. Expand "SQL Server Object Explorer"
  5. Click "Scripting"
  6. Scroll to "Table And View Options"
  7. Click "Script Indexes" and set it to "True"
  8. Now right click on your Table in Object  Explorer
  9. Now Click "Script Table As" -> "CREATE TO" => "New Query Editor Window"
  10. All indexes for the table will be in the script generated.

 

NOTES:

There are no notes on this topic.