Monday, 24 August 2009

SQL Server - Schema Information

SQL Server 2000:

-- What columns does this table have?
SELECT SC.NAME AS ColumnName
FROM SYSOBJECTS SO
INNER JOIN SYSCOLUMNS SC ON SO.ID = SC.ID
WHERE SO.XTYPE = 'U' AND SO.NAME = 'ReportRole'
ORDER BY SO.NAME, SC.NAME

-- What user tables exist in this database?
SELECT SO.NAME AS TableName
FROM SYSOBJECTS SO
WHERE SO.XTYPE = 'U'
ORDER BY SO.NAME


-- Does this table have any identity column?
IF (OBJECTPROPERTY(OBJECT_ID('tableName'), 'TableHasIdentity') = 1)
PRINT 'Table has an identity column'
ELSE PRINT 'Table does not have an identity column'

SQL Server 2005:

No comments: