SQL Server tips

1. Get exception message

Select description from Master .. sysmessages where error = @ Error

2. query the table column names based on the primary key.

Select name from syscolumns where id = object_id ('table name') and colid in (
Select colid from sysindexkeys where object_id ('table name') = ID and indid in (
Select indid from sysindexes where object_id ('table name') = ID and name in (
Select name from sysobjects where xtype = 'pk' and parent_obj = object_id ('table name ')
)))

3

Exec sp_msforeachtable
@ Precommand = N'
Create Table ##(
Id int identity,
Table Name sysname,
Number of fields int,
Number of records int,
Reserved space nvarchar (10 ),
Use space varchar (10 ),
Index space varchar (10 ),
Unused space varchar (10 ))',
@ Command1 = n' insert ## (Table Name, number of records, reserved space, space used, index space used, unused space) exec sp_spaceused ''? ''
Update ## set field COUNT = (select count (*) from syscolumns where id = object_id (''? '') Where id = scope_identity ()',
@ Postcommand = n' select * from # Where table name = ''[Table]'' order by ID drop table ##'
4

Select a. XX, A. XX from a where not exists (select 1 from B where a. ID = B. ID)
And select a. XX, A. XX from a where not exists (select * from B where a. ID = B. ID)

5. view the number of records per table

Exec sp_msforeachtable 'select ''? ''As ''table name'', (select count (1) from ?) As ''' number of rows '''

Select distinct O. Name, I. rows from sysobjects o
Inner join sysindexes I
On O. ID = I. ID
Where xtype = 'U' and objectproperty (O. ID, N 'isusertable') = 1 and I. Rows> 0

Select distinct A. Name, C. Rows
From sysobjects A, syscolumns B, sysindexes C
Where a. ID = B. ID and A. ID = C. ID
And a. type = 'U' and C. Rows <> 0

Create Table DBO. # (tab_name sysname)

Exec sp_msforeachtable 'If (select count (*) from (select top 1 * from ?) A)> 0 insert into # values (''? '')'

Select count (*) from #