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

临近年终，在工作之余对工作和学习中遇到的问题以及常用的一些知识点做了些整理，以备后用。本文涉及的内容为数据库,算是对开发总结(1)---数据库一文的补充。 1 对于主键设置了Identity的表，在删除表中数据后再往表中插入数据，Identity列不是从1起始了，如

临近年终，在工作之余对工作和学习中遇到的问题以及常用的一些知识点做了些整理，以备后用。本文涉及的内容为数据库,算是对开发总结(1)---数据库一文的补充。

1 对于主键设置了Identity的表，在删除表中数据后再往表中插入数据，Identity列不是从1起始了，如果想删除数据后Indentity列仍从1起始，可以用下面代码来删除数据。

<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>

2 判断指定表在数据库中是否存在

<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>

3 判断指定列在指定表中是否存在

<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>

4 在编写代码生成器之类的程序的时候，通常需要取出数据库中所有的表名以及表中字段的一些基本信息，如字段长度、字段类型、描述等。实现上面要求的sql语句如下：

<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>

5 在存储过程中使用事务

<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>

6 清除数据库日志

<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>

还有一种比较简单的方法是分离数据库，删除日志文件，再附加数据库，这样产生的日志文件只有500多k。

---

下面介绍几个常用的系统存储过程和函数

7 db_name()  得到数据库名称

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

8 object_id 可以得到对象在系统中的编号，对象包括表、视图、存储过程等。如果不存在返回null，所以也可以用来判断表是否存在。

<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>

9 sp_helptext 用来得到视图、存储过程等对象的文本，可以很快速找到，不过会改变视图或存储过程的格式。所以这个系统存储过程我通常都是用来查看，如果要修改一个存储过程我还是会通过树形菜单去找到存储过程然后修改保存。

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

10 parsename，可以得到对象名称的指定部分，该函数有两个参数，第一个为对象名称，第二个为指定部分的代号。

<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>

先就写这么多吧，后面整理出来的会陆续补上。