SqlServer数据库的语句及一些操作整理

<span>truncate table </span><span>tablename
</span><span>DBCC </span><span>CHECKIDENT</span><span>(</span><span>tablename</span><span>,</span><span>RESEED</span><span>,</span><span>1</span><span>)</span>

<span>if </span><span>exists(</span><span>select name from </span><span>sysobjects </span><span>where name</span><span>=</span><span>'tablename' </span><span>and </span><span>type</span><span>=</span><span>'u'</span><span>)</span>

<span>if </span><span>exists(</span><span>select </span><span>* </span><span>from </span><span>sys.columns</span><span>,</span><span>sys.tables 
      </span><span>where </span><span>sys.columns</span><span>.</span><span>object_id </span><span>= </span><span>sys.tables</span><span>.</span><span>object_id
      </span><span>and </span><span>sys.tables</span><span>.</span><span>name</span><span>=</span><span>'tablename' </span><span>and </span><span>sys.columns</span><span>.</span><span>[name]</span><span>=</span><span>'columnname'</span><span>)</span>

<span>--取数据库中表的集合
</span><span>select </span><span>* </span><span>from </span><span>sysobjects </span><span>where </span><span>xtype</span><span>=</span><span>'u' </span><span>order by name

</span><span>--取表中字段的一些基本信息
</span><span>select 
    </span><span>sys.columns</span><span>.</span><span>name</span><span>,  </span><span>--字段名
    sys.types</span><span>.</span><span>name as </span><span>typename</span><span>, </span><span>--字段类型
    sys.columns</span><span>.</span><span>max_length</span><span>,    </span><span>--字段长度
    sys.columns</span><span>.</span><span>is_nullable</span><span>,    </span><span>--是否可空
    </span><span>(</span><span>select 
        </span><span>count</span><span>(*) 
    </span><span>from 
        </span><span>sys.identity_columns 
    </span><span>where 
        </span><span>sys.identity_columns</span><span>.</span><span>object_id </span><span>= </span><span>sys.columns</span><span>.</span><span>object_id 
    </span><span>and 
        </span><span>sys.columns</span><span>.</span><span>column_id </span><span>= </span><span>sys.identity_columns</span><span>.</span><span>column_id
    </span><span>) </span><span>as </span><span>is_identity </span><span>,</span><span>--是否自增

    </span><span>(</span><span>select 
        value 
    from 
        </span><span>sys.extended_properties 
    </span><span>where 
        </span><span>sys.extended_properties</span><span>.</span><span>major_id </span><span>= </span><span>sys.columns</span><span>.</span><span>object_id 
    </span><span>and 
        </span><span>sys.extended_properties</span><span>.</span><span>minor_id </span><span>= </span><span>sys.columns</span><span>.</span><span>column_id
    </span><span>) </span><span>as </span><span>description  </span><span>--注释
</span><span>from 
    </span><span>sys.columns</span><span>, </span><span>sys.tables</span><span>, </span><span>sys.types
</span><span>where 
    </span><span>sys.columns</span><span>.</span><span>object_id </span><span>= </span><span>sys.tables</span><span>.</span><span>object_id 
</span><span>and 
    </span><span>sys.columns</span><span>.</span><span>system_type_id</span><span>=</span><span>sys.types</span><span>.</span><span>system_type_id 
</span><span>and 
    </span><span>sys.tables</span><span>.</span><span>name</span><span>=</span><span>'tablename'
</span><span>order by </span><span>sys.columns</span><span>.</span><span>column_id</span>

<span>create procedure </span><span>procname
</span><span>as
begin tran  
    </span><span>--执行sql语句

</span><span>if </span><span>@@ERROR</span><span>!=</span><span>0  
</span><span>begin  
    rollback tran   </span><span>--失败
</span><span>end  
else  
begin  
    commit tran   </span><span>--成功
</span><span>end  </span>

<span>DUMP TRANSACTION </span><span>DatabseName </span><span>WITH </span><span>NO_LOG
</span><span>BACKUP </span><span>LOG </span><span>DatabseName </span><span>WITH </span><span>NO_LOG 
</span><span>DBCC  </span><span>SHRINKFILE</span><span>(</span><span>DatabseLogName</span><span>,</span><span>1</span><span>) 
</span><span>--DatabseName为数据库名称
--DatabseLogName为日志文件名，可以通过下面语句得到
--select name from sysfiles </span>

<span>select </span><span>db_name</span><span>()    
</span><span>Test
</span><span>(</span><span>1 行受影响</span><span>)</span>

<span>select </span><span>object_id</span><span>(</span><span>'objectname'</span><span>)
</span><span>--判断表是否存在
</span><span>if  </span><span>object_id</span><span>(</span><span>'tablename'</span><span>) is not null</span>

<span>sp_helptext </span><span>'objectname'</span>

<span>select </span><span>parsename</span><span>(</span><span>'oec2003.databasename.dbo.tablename'</span><span>,</span><span>1</span><span>)    
</span><span>--对象名称返回tablename
</span><span>select </span><span>parsename</span><span>(</span><span>'oec2003.databasename.dbo.tablename'</span><span>,</span><span>2</span><span>)    
</span><span>--Schema名称返回dbo
</span><span>select </span><span>parsename</span><span>(</span><span>'oec2003.databasename.dbo.tablename'</span><span>,</span><span>3</span><span>)    
</span><span>--数据库名称返回databasename
</span><span>select </span><span>parsename</span><span>(</span><span>'oec2003.databasename.dbo.tablename'</span><span>,</span><span>4</span><span>)    
</span><span>--服务器名称返回oec200</span>