SQL»ñÈ¡ËùÓÐÊý¾Ý¿âÃû¡¢±íÃû¡¢´¢´æ¹ý³ÌÒÔ¼°²ÎÊýÁбí
SQL»ñÈ¡ËùÓÐÊý¾Ý¿âÃû¡¢±íÃû¡¢´¢´æ¹ý³ÌÒÔ¼°²ÎÊýÁбí
1.»ñÈ¡ËùÓÐÊý¾Ý¿âÃû:
(1)¡¢Select Name from Master..SysDatabases order by Name
2.»ñÈ¡ËùÓбíÃû:
(1)¡¢Select Name from SysObjects Where XType=''U'' orDER BY Name
XType=''U'':±íʾËùÓÐÓû§±í;
XType=''S'':±íʾËùÓÐϵͳ±í;
(2)¡¢SELECT name from sysobjects WHERE type = ''U'' AND sysstat = ''83''
×¢Ò⣺һ°ãÇé¿öÖ»ÐèÒªtype = ''U''£¬µ«ÓÐʱºò»áÓÐϵͳ±í»ìÔÚÆäÖУ¨²»ÖªµÀʲôÔÒò£©£¬¼ÓÉϺóÃæÒ»¾äºó¾ÍÄÜɾ³ýÕâЩϵͳ±íÁË
3.»ñÈ¡ËùÓÐ×Ö¶ÎÃû:
(1)¡¢Select Name from SysColumns Where id=Object_Id(''TableName'')
(2)¡¢SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length from syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND "syscolumns.id = object_id(''tableName'')
×¢Òâµã£º
£¨a£©ÕâÀïΪÁËÖصãÍ»³öijЩÖØÒªÄÚÈÝ£¬Ñ¡È¡ÁËÆäÖм¸ÏîÐÅÏ¢Êä³ö¡£
£¨b£©syscolumns±íÖÐÖ»º¬ÓÐÊý¾ÝÀàÐͱàºÅ£¬Òª»ñÈ¡ÍêÕûµÄÃû×ÖÐèÒª´Ósystypes±íÖÐÕÒ£¬Ò»°ãÓû§Ê¹ÓõÄÊý¾ÝÀàÐÍÓÃxusertype¶ÔÓ¦±È½ÏºÃ£¬²»»á³öÏÖÒ»¶Ô¶àµÄÇé¿ö¡£
£¨c£©syscolumns.lengthµÃµ½µÄÊÇÎïÀíÄÚ´æµÄ³¤¶È£¬ËùÒÔnvarcharºÍvarcharµÈÀàÐÍÔÚÊý¾Ý¿âÖеÄÏÔʾÊÇÕâ¸öµÄÒ»°ë¡£
4¡¢µÃµ½±íÖÐÖ÷¼üËù°üº¬µÄÁÐÃû:
SELECT syscolumns.name from syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id = object_id(''tablename'') AND sysobjects.xtype = ''PK'' AND sysobjects.parent_obj = syscolumns.id AND sysindexes.id = syscolumns.id AND sysobjects.name = sysindexes.name AND sysindexkeys.id = syscolumns.id AND sysindexkeys.indid = sysindexes.indid AND syscolumns.colid = sysindexkeys.colid
×¢Ò⣺ÕâÊÇÔÚ4ÕÅϵͳ±íÖÐÑ°Õҵģ¬¹Øϵ±È½Ï¸´ÔÓ£¬´óÖ¿ÉÒÔ±íʾΪ£º
syscolumnsÖдæÓбíÖеÄÁÐÐÅÏ¢ºÍ±íid£¬sysobjects±íÖдæÓÐÖ÷¼üÃû×Ö£¨¼´PK_TableÀàËÆ£©ºÍ±íid£¬sysindexesÖдæ ÓÐÖ÷¼üÃû×ֺͱíidºÍindex±àºÅ£¬sysi
Ïà¹ØÎĵµ£º
˼·:SQLÀᄀ¿ÉÄܵIJÉÓÃÁ½¸öÒýºÅ±ÕºÏ±äÁ¿;¶Ô±äÁ¿ÖеÄÒýºÅתÒå;¶Ô²»ÄܲÉÓÃ2¸öÒýºÅ±ÕºÏµÄ±äÁ¿,ÏÈÔ¤ÏÈÅжϱäÁ¿ÀàÐÍ;
·½°¸:
1 ËùÓзÇÊýÖµÔËËã(Èç×Ö·û,¼òµ¥ÊýÖµÐ͵ȵÈ)µÄSQL±äÁ¿¶¼¼ÓÒýºÅ;
2 ËùÓзÇÊýÖµÔËËãµÄSQL±äÁ¿µÄÖµ¶¼²ÉÓÃmagic_quotes_gpc» ......
--------¼ì²âÊý¾Ý¿âÊÇ·ñ´æÔÚ
use master
go
if exists(select * from sysdatabases where name='DBName')
drop database DBName
-----´´½¨Êý¾Ý¿â
create database DBName
on
(
/*Êý¾ÝÎļþµÄ¾ßÌåÃèÊö*/
name='DBName_data', -----Ö÷Êý¾ÝÎļþµÄÂß¼Ãû³Æ
filename='D:\project\DBName_data.mdf', ......
¹Ì¶¨·þÎñÆ÷½ÇÉ«
sysadmin ¿ÉÒÔÔÚ SQL Server ÖÐÖ´ÐÐÈκλ¡£
serveradmin ¿ÉÒÔÉèÖ÷þÎñÆ÷·¶Î§µÄÅäÖÃÑ¡Ï¹Ø±Õ·þÎñÆ÷¡£
setupadmin ¿ÉÒÔ¹ÜÀíÁ´½Ó·þÎñÆ÷ºÍÆô¶¯¹ý³Ì¡£
securityadmin ¿ÉÒÔ¹ÜÀíµÇ¼ºÍ CREATE DATABASE ȨÏÞ£¬»¹¿ÉÒÔ¶ÁÈ¡´íÎóÈÕÖ¾ºÍ¸ü¸ÄÃÜÂë¡£
processadmin ¿ÉÒÔ¹ÜÀíÔÚ SQL Server ÖÐÔËÐеĽø³Ì¡£
......
Ò»¡¢ÎÊÌâÒý³ö£º
µ±Ä³Ò»Ì¨SQL ServerʵÀýÉÏ´´½¨Óжà¸öÊý¾Ý¿âµÄʱºò£¬Èç¹ûijһ¸öÊý¾Ý¿â¹ý¶ÈÕ¼ÓÐϵͳ×ÊÔ´£¬ÍùÍù»áµ¼ÖÂÆäËüÊý¾Ý¿âÐÔÄÜÊܵ½ÑÏÖØÓ°Ï죬ÉõÖÁ´ó´ó½µµÍÕû¸öÊý¾Ý¿âµÄÐÔÄÜ¡£
ÕâÖÖÇé¿öÏ£¬Èç¹ûÓÐÒ»ÖÖ¼¼Êõ»òÕß·½·¨¿ÉÒÔÏÞÖÆÊý¾Ý¿â×ÊÔ´µÄÕ¼ÓÐÁ¿£¬¸øÓëÒ»¸ö·åÖµ£¬´Ó¶ø¸øÓëÆäËüµÄÊý¾Ý¿â×ã¹»µÄ×ÊÔ´¡£ÄÇôÕâ¸öÎÊÌâ¾Í¿É ......