create table permission

Hi,
Is there any query available to check the existence of 'CREATE TABLE' permission in a database
?
Please advice,
Thanks,
MiraJI really need to get my hands on an SQL Server ER Diagram...but start with syspermissions

CREATE Table permission

I gave a user explicit permission to create table, but still getting
this error:

Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors

Do I need to create a new schema?

--sharifSharif Islam (mislam@.npspam.uiuc.edu) writes:

Quote:

Originally Posted by

I gave a user explicit permission to create table, but still getting
this error:
>
Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors

Sounds like you are using some graphical tool in SSMS. Those tools are
of poor quality, and I recommend that you try CREATE TABLE instead.

But only CREATE TABLE may not be sufficient. The user may also need ALTER
permission on the schema (for instance dbo).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

create sqlexpress mdf compat. with sql2k

I have a script to create a db. it only uses features available to sql2k. I only have sqlexpress, but want to distribute a db to someone with sql2k. Can I set a setting or something to allow me to detach my db and attach it to the sql2k server, or create the db as sqk2k compat.?

thanks

Databases created against SQL Server 2005 cannot be detached and attached to SQL Server 2000. You should be able do an export/import.

Dan

create index

I need to check if an index available on table T1 and field F1. If not, create a non-clustered on F1. How can I do this in a stored procedure?

Something like this:

SELECTsc.name,*FROMsysindexkeys skJOINsyscolumns scON sk.colid = sc.colidand sk.id = sc.idWHEREsk.id =object_id('Activity')AND sc.name ='activityid'

create index

I need to check if an index available on table T1 and field F1. If not, create a non-clustered on F1. How can I do this in a stored procedure?

SELECT

C.[Name] AS [IndexedColumns]

FROM sys.indexes I

INNER JOIN sys.index_columns IC ON (I.index_id = IC.index_ID)

INNER JOIN sys.columns C ON (IC.column_ID = C.column_ID)

WHERE

OBJECT_NAME(I.OBJECT_ID) = 'StoreContact'

|||

Actually it is a bit more than that. There are a few things you have to take care of, including included columns. This query will do it:

use tempdb
go
drop table t1
go
create table T1
(
T1Id int primary key,
F1 int,
F2 int,
F3 int,
F4 int
)
create index T1_F2 on t1(f2)
create index T1_F3andF4 on t1(f3,f4)
create index T1_F1includeF4 on t1(f1) include (f4)
go

--the first subquery gets the primary columns in the index. So set the columns
--you want to match in the IN clause. Then set the number to match the number
--of matches you desire.

--the second does the included columns, something you have to consider for 2005
--because it could look like you have an index that you don't actually have if
-- create index T1_F1includeF4 on t1(f1) include (f4)
--is created instead of
--create index T1_F1includeF4 on t1(f1,f4)

select i.name, type_desc,is_unique
from sys.indexes as i
where object_name(i.object_id) = 'T1'
and (select count(*)
from sys.columns as sc
join sys.index_columns as ic
on sc.object_id = ic.object_id
and sc.column_id = ic.column_id
and ic.is_included_column = 0
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and sc.name in ('f1')) = 2 --match # of cols in in clause
and (select count(*)
from sys.columns as sc
join sys.index_columns as ic
on sc.object_id = ic.object_id
and sc.column_id = ic.column_id
and ic.is_included_column = 1
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and sc.name in ('')) = 0 --match # of cols in in clause
go

|||

Jim, if your application is large enough you will soon run into maintainance nightmare if you are not sure whether you have the specific index on specific table. Checking whether the index exists or not and creating it is one-time solution.

Create some table that tracks your database version. If you need to do any schema change, change the db version correspondingly.

For a fixed db version, your table schemas should be unambigous. You might have an sql file for each table, which should include the create table command AND create index commands. Say, if you have added an index(iDATE) for table T on version 6.35, then all the clients that have db version 6.35 should have iDATE on T, and NONE should have such index if their db version is < 6.35.
If you are interested, post your application specifics, and we could discuss specific version changing schemas.
Good luck.

|||

Jim, if your application is large enough you will soon run into maintainance nightmare if you are not sure whether you have the specific index on specific table. Checking whether the index exists or not and creating it is one-time solution.

No doubt. I use this kind of code from my data modeling tool to create indexes if they are on the model but not in the actual database.