Wednesday, February 7, 2007

SQL Server - Find primary keys in a table

To find primary key in a given table :

Option 1: sp_pkeys 'yourtablename'

Option 2: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = 'yourtablename' and constraint_type = 'primary key'

Option 3: sp_help 'yourtablename'

To find all primary keys in database:

Use yourdatabasename
select c.COLUMN_NAME ,c.CONSTRAINT_NAME,C.table_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

These would work for both SQL Server 2K and SQL Server 2K.

Business Intelligence Studio - Installation error

After installing SQL Server developer edition, Business Intelligence Studio starts looking for file devenv.exe.

To resolve this issue:
1. You should make sure that Visual Studio is still installed.
2. If you didn't previously have VS installed, the BI Dev Studio installation will install a VS shell called Visual Studio Premier Partner Edition.
3. Look in Add or Remove Programs for “Microsoft Visual Studio Premier Partner Edition”. If you don't find any entry for Visual Studio go to the location for SQL Server setup and run \Setup\vs_setup.exe. (Usually in CD #2 under setup)
4. After VSShell is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory: start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS