Tag Info

New answers tagged

1

These two queries may help you. The first will list all of the tables and indexes on those tables in your database. If the table does not appear in the list is does not have any indexes defined on it. These queries assume SQL Server version 2005 or newer. SELECT I.name AS IndexName , T.name AS TableName , I.is_primary_key AS IsPrimaryKey ...


1

I am not sure if you're interested in all constraints but INFORMATION_SCHEMA.TABLE_CONSTRAINTS doesn't seem to return the DEFAULT constraints -- TABLE_CONSTRAINTS (Transact-SQL) CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY This query will do a simple count against the sys.objects DMV: select COUNT(*) from sys.objects o where o.type_desc like ...


1

No, something is incorrect. The check on sys.indexes should return a row even if your table has no indexes. The heap still has a record in sys.indexes with a type_desc of 'HEAP' and type of 0. I think you probably need to make sure you are in the right database context since OBJECT_ID() and sys.objects are database-specific. Try this: USE MyDatabase ...


1

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema ) do $$ declare r record; BEGIN FOR r IN ( SELECT 'ALTER TABLE '|| tc.table_name||' VALIDATE CONSTRAINT '||tc.constraint_name||';' X FROM information_schema.table_constraints AS tc JOIN ...


4

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition CREATE TABLE Test( ColA [nvarchar](50) NOT NULL, COLB [nvarchar](50) NULL) The first three insert ...



Top 50 recent answers are included