December 9, 2008 at 11:03 am
Is there a way to tell whether and index/constraint name was created by SQL Server or by physically creating the name. i.e.alter table tbl add primary key(id) compared toalter table tbl add constraint pk primary key(id)
December 9, 2008 at 1:39 pm
You can query sys.objects and anything with a is_ms_shipped = 1 is a system created object.
December 9, 2008 at 2:16 pm
Didn't seem to work. Both versions have is_ms_shipped = 0
IF OBJECT_ID('tbl') IS NOT NULL
DROP TABLE tbl
GO
CREATE TABLE tbl( id INT NOT NULL)
GO
ALTER TABLE tbl ADD PRIMARY KEY(id)
GO
SELECT * FROM sys.objects WHERE parent_object_id = OBJECT_ID('tbl')
GO
BEGIN
DECLARE @IndexName sysname, @sql NVARCHAR(MAX)
SELECT @IndexName = NAME FROM sys.objects WHERE parent_object_id = OBJECT_ID('tbl')
SET @sql = N'ALTER TABLE tbl DROP CONSTRAINT ' + @IndexName
EXEC sp_executesql @sql
END
GO
ALTER TABLE tbl ADD CONSTRAINT pk PRIMARY KEY(id)
GO
SELECT * FROM sys.objects WHERE parent_object_id = OBJECT_ID('tbl')
GO
DROP TABLE tbl
GO
December 9, 2008 at 2:30 pm
is_ms_shipped - Object is created by an internal Microsoft SQL Server component., it will be 0 if you eliminate the constraint name during creation. So it won't be helpful in your case.
In Our system, according to our naming standard, Pks will always be tablename_PK. But the system created PK will be PK__tablename__8digithexvalue.
December 9, 2008 at 2:50 pm
You can use the column 'is_system_named' in the tables:
sys.default_constraints
sys.foreign_keys
sys.key_constraints
sys.check_constraints
Not sure where you can find the same thing for indexes that are not constraints. I do know that you can use SMO and use the Index.IsSystemNamed Property - just don't know where that is actually stored.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 10, 2008 at 6:02 am
Thank you very much, that's what I was looking for. I'm not worried about indexes because we do name them and not sure if you can make an index without a name. Either way thanks.
December 10, 2008 at 8:47 am
You are welcome.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply