How can you know if a table has a certain column

Lets say we have a table named Users and we want to know if such table already has a column named RetailerId.

That could be accomplished with the following script:

IF EXISTS (

SELECT * FROM dbo.syscolumns WHERE id = (select id from dbo.sysobjects where type = ‘u’ and name = ‘Users‘) AND name = ‘RetailerId‘

)

The id of the RetailerId column is the same as the table’s id.

SQL Server 2000 – objmgr80.xml

if you look at the following screenshot , you’ll see a highlighted file named objmgr80.xml.

This file contains information about T-SQL variables (you know, the @@ variables)

UNIQUE column howto

There are three ways to generate a UNIQUE column on a table.

1. When creating the table definition. For example:

CREATE TABLE [dbo].[Factura] (
[FacturaId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExamenId] [int] NOT NULL UNIQUE,
[Fecha] [datetime] NOT NULL
)

2. After creating the table as follows:

CREATE TABLE [dbo].[Factura] (
[FacturaId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExamenId] [int] NOT NULL ,
[Fecha] [datetime] NOT NULL
) ON [PRIMARY]

ALTER TABLE Factura
ADD UNIQUE (ExamenId)

3. Or, like this:

ALTER TABLE Factura
ADD CONSTRAINT UN_examenId UNIQUE (ExamenId)

In the first and second case SQL Server will create a deafult label for the unique column, for example something like UQ__Factura__29572725 .
In the third case we are explicitly applying the label UN_examenId for our unique column.

Apparently there’s another method to create a unique column, but I haven’t tested it. It’s as follows:

CREATE TABLE [dbo].[Factura] (
[FacturaId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExamenId] [int] NOT NULL CONSTRAINT UN_Factura UNIQUE ,
[Fecha] [datetime] NOT NULL
) ON [PRIMARY]

Filtering a DataTable

DataTable dt = ds.Tables["BizRoles"];
DataRow[] dr = dt.Select(“RoleID = 45″);

SELECT FROM Select (“Memory” table)

This, I think, may be a different way of creating a temp table.

use Northwind

SELECT c.CustomerId, c.CompanyName
FROM (Select * From Customers) c

We are creating a “memory” table and then selecting its data.

SQL Server – FULL OUTER JOIN

Given the table Packages

unitId
———–
183
184
187
188

and the table Transactions

unitId
———–
187
188
189
190

The following query:

SELECT ISNULL(p.unitId, t.unitId) as UnitId
FROM Packages p
FULL OUTER JOIN Transactions t ON p.unitId = t.unitId
WHERE p.unitId IS NULL OR t.unitId IS NULL

Will return this:

UnitId
———–
183
184
189
190

This is the negation of the intersection, just to say it somehow.

SQL Server – Temp tables

This example

SELECT UnitCode, UnitName, UnitId, UnitParentId
INTO #ResultSet
FROM Units JOIN Parents ON (Units.UnitChildId = Parents.UnitId)
WHERE OrderId = 11 ORDER BY UnitParentId

Will create a temporal table named #ResultSet. This table will be available only on the session where it was created and as long as the session is active.

ASP – Response.Write() and Response.Redirect()

If we have the following code:

Response.Write("hello world")
Response.Redirect(”page2.asp”)

For some reason (an asp bug maybe) the redirect wont work.

But if we modify the code as follow:

Response.Redirect("page2.asp")
Response.Write(”hello world”)

Then it will work.

Edited: That’s not 100% true, sometimes it works, sometimes it doesn’t. In either case, if sometime you have a Redirect that is not working, you should keep this in mind.

ASP – POSTing

Post data from one page to another.

When posting data to a page we are requesting that page, in other words, the page is requested.

default.asp

<form name=”employeeForm” method=”post” action=”page1.asp”>
<input name=”txtName” type=”text” value=”Bob” />
<input type=”submit” name=”btnOK” value=”OK” />
</form>

page1.asp

<%
‘ Response.Write(Request(”txtName”))

‘ name = Request.Form(”txtName”)
‘ name = Request(”txtName”)

‘ Response.Write(name)

Response.Write(”<br /> Request.Form(txtName) = ” & Request.Form(”txtName”))
Response.Write(”<br /> Request(txtName) = ” & Request(”txtName”))
%>

Both Request.Form(txtName) and Request(txtName) are the same.

ASP – QueryString

Extra information that is passed along in the GET of the HTTP request, so the GET is the actual url.

page1.asp

<a href="page2.asp?myValue=Andee">Click me</a>

page2.asp

<%
‘ Dim queryStringValue

‘ queryStringValue = Request.QueryString(”myValue”)
‘ queryStringValue = Request(”myValue”)

Response.Write(”<br /> Request.QueryString(myValue) = ” & Request.QueryString(”myValue”))
Response.Write(”<br /> Request(myValue) = ” & Request(”myValue”))
%>

Both Request.QueryString() and Request() are the same