I wanted my first programming blog to be of a little more import than a few lowly Sequel Scripts, however I found these to be invaluable over the weekend. Mostly, I put them here so that I can reference them in the future, but it would be delighted should it help somone else out of a bind.
/* Search Database Collection for Column Name. Returns a Collection of Names. */
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like ‘%MyText%’ )
/* Search a Table Collection of Columns. Returns a Collection of Names. */
SELECT COLUMN_NAME FROM information_schema.Columns WHERE table_name=’[MyTable]‘ AND COLUMN_NAME LIKE ‘%MyText%’
/* Create a copy of a table with structure (No PK) */
SELECT * INTO [NewTable] FROM [ExistingTable]
/* Create a copy of a table from another database with structure (No PK) */
SELECT * INTO [NewTable] FROM [DatabaseName].dbo.[ExistingTable]
/* Create a copy of a table from another database with structure (No PK) */
SELECT COUNT( * ) as TOTAL_RECORD FROM [ExistingTable] WHERE [ExistingField]=’[Constraint]‘
/* Update Column(s) in the target table based upon a different table with a common index(s) */
UPDATE [MyTable1]
SET [MyTable1].[ColumnName]=[MyTable2].[ColumnName]
FROM [MyTable1], [MyTable2]
WHERE [MyTable1].[PKColumn]=[MyTable2].[PKColumn]
