Table with a lot of columns - easy parsing to do a select statement

--USE FOR XML PATH - MUST BE SQL2005 OR HIGHER.


SELECT 'SELECT ' + '''' + so.Name +''', as TblName ' , (SELECT sc.Name + ', ' AS 'data()' FROM dbo.SysColumns sc WHERE so.ID = sc.ID FOR XML Path('')) AS PARSESTRING FROM Sysobjects so where so.xtype = 'U'
and so.name = 'mytablewithlotsofcolumns'

Then just finish it off with adding the from in the parser string.

Comment on other methods that you have used.

MSSQL - syscomments

Sometimes in SQL I am looking for a procedure that references a certain text string or table or column.

The best script that I have come up when looking for procedural string references is a simple select against the syscomments and sysobjects tables.

Syntax
Select a.name from sysobjects a
join syscomments b
on
a.id = b.id
and a.xtype = 'P' 
where b.text like '%mytextstring%'

Examlple
Select a.name from sysobjects a
join syscomments b
on
a.id = b.id
and a.xtype = 'P'
where b.text  like '%xp_sendmail%'

The example will look for any procedure within the current database that has the text string of xp_sendmail in it.  Once it returns the object names then I can review the procedural code for those procedures that returned.

I find this very useful when I am trying to find procedures that use "xp_sendmail" or other items like referencing a certain value or hard coded items from old code.  I also find this very useful when I need to write a new procedure for some reason or another and there may already be a similar procedure that I can copy part of the code from.  Especially if you are new to an organization this is very useful as a lot of times you don't always know what all of the procedures do, this is a handy way to look for specific key words and values to see if a procedure is similar in nature to what you are trying to accomplish.

sp_help tablename

Using the following syntax will provide details on a table.

Syntax:  sp_help 'tablename'

Example: sp_help 'authors'


This system procedure is most useful in obtaining details of columns within a table.  Their names and datatypes.

MSSQL - Use database

Query analyzer.
The first step in writing any query is ensuring you are in the right database.  If you are connected to the correct server and know which database you want to connect to use the following statement to ensure you are in the correct database.

Syntax:
Use databasename
Go

Example
Use pubs
Go

Execute the statement by hitting F5 or the execute button in the query analyzer window. After executing the statement you should see the database name in the explorer window change to the database you keyed. 

Give it a try. 

Restoring single tables from a backup

Unless you are using replication or some other third party tool, it is virtually impossible to restore a single table from an sql backup. I wonder when Microsoft will release a single table backup and restore function into their SQL releases. This is a long awaited function that I personally cannot wait to see.

For now, I backup the tables I need through a DTS package or SQL script. It would be nice to have the option under management studio to be able to select single or multiple tables for backup. An option that would give you the choice of backing up tables and data, triggers first dependancy objects. User Permissions as well. Many times when I am building new applications or enhancing current supported applications, I have to create backup scripts for existing data. I would like to see a feature that exists for this. I want to be able to have a timing mechanism in place to drop the backed up data within 3 to 5 days after a deploy.

Has anyone else encountered this what do you recommend? What do you normally do?

Another item for this feature would be to drop and re-add constraints if the data needs to be merged back in. My deploys usually go pretty smooth but it would be nice for the occassional restore not to have to include the whole database. I guess that is where replication snapshots come into play, but they are not feature strong and they don't exist on earlier versions of sql.

JOINS
Everyone writes articles on the definitions of joins and examples of each.

Here are mine.

Join (simple join)
A simple join is when data in table1 and data in table2 match on the column being joined to.
Syntax Example:
Select
*
from
Employee a
Join
Employee_Salary b
on
a.EmployeeId = b.EmployeeId

Left Join
Get all of the data in table1 that has a like value in table2.
Select
*
from
Employee a
Left Join
Employee_Salary b
on
a.EmployeeId = b.EmployeeId


Left Outer Join
Get all of the data in table1 regardless if it has a match in table2.
Select
*
from
Employee a
Left outer Join
Employee_Salary b
on
a.EmployeeId = b.EmployeeId

Right Join
Get all of the data in table2 that has a like value in table1
Select
*
from
Employee a
Right Join
Employee_Salary b
on
a.EmployeeId = b.EmployeeId

Right Outer Join
Get all of the data in table2 regardless if it has a match in table1.
Select
*
from
Employee a
Right Outer Join
Employee_Salary b
on
a.EmployeeId = b.EmployeeId

Full outer join
Get all of the data in table1 and table2 regardless of matching data.
Select
*
from
Employee a
Full Outer Join
Employee_Salary b
on
a.EmployeeId = b.EmployeeId

Joins can make or break your query, one wrong join and you could end up with invalid data.

SQL DATES

Dates to me are one of the most annoying items within SQL. Getting dates, performing date validation checks, return sets based on dates, converting dates, transforming dates. I find this can be a real pain at times.

Every SQL dba knows the
Select getdate() - will return the current system date.

Select getdate()-1 will return the current date - 1 day

Select getdate()+1 will return the current date + 1 day
these are pretty simple.

for the most part dates are simple to work with, but they are just annoying when you need to convert them. My biggest peeve is working in a database that has columns that are datetime columns and other columns that are a different datatype but are date columns like Int for example. Or someone has created a date column as a char(8) or something of that nature. I find it frustrating when the date columns take on multiple data types. Even in circumstances where they could of been consistent.

select getdate() AS CurrentDate,
'Q'+DATENAME(quarter, convert(char, getdate())) As Quarter,
DATENAME(year, convert(char, getdate())) As Year,
DATENAME(month, convert(char, getdate())) as Month,
DATENAME(Dayofyear, convert(char, getdate())) as DayofYear,
DATENAME(day, convert(char, getdate())) as Day,
DATENAME(week, convert(char, getdate())) as Week,
DATENAME(WEEK, GETDATE()) AS CurrentWeek,
DATENAME(MONTH, GETDATE()) AS CurrentMonth

SELECT convert(varchar,DATEADD(mm, DATEDIFF(mm,0,getdate())-25, 0),112) as [First Day of Previous Month 24 months ago]

more to come....