SQL Server tables: SELECT COUNT (1) from sysobjects where xtype= ' U ' views: select COUNT (1) from sysobjects where xtype= ' V ' number stored procedures Selec T count (1) from sysobjects where xtype= ' P 'SELECT * from sysobjects WHERE (xtype = ' U ')C = CHECK ConstraintD = defaults or DEFAULT constraintsF = FOREIGN KEY constraintL = LogFN = Scalar functionIF =
Tags: SQL Server tables, views, indexesFirst, the table related1. CreateUse[test] Go setansi_nullson go setquoted_identifieron go CREATETABLE[dbo] . [Ceshi] ([Id][int]notnull, [Name][varchar (]null), CONSTRAINT[PK_Ceshi] primarykeyclustered ([Id]asc) with (Pad_index=off,statistics_ norecompute=off,ignore_dup_key=off,allow_row_locks=on, Allow_page_locks=on) on[pri
Long unused SQL Server has been using NAVICAT to handle a variety of databases, it feels convenient to use, but because of the actual need to use SQL Server to create a new database, but unexpectedly encountered previously never encountered problems.After the construction of the table, want to modify some properties, t
First, the table structure of a table needs to be copied from one data (a) library to another database (B). The operation step is as follows:① in Database A, select the tables that you want to replicate. Right-Select the Edit table script to creat to the new Query editing window.② in the newly opened Query window, use CTRL + A to copy the script after you select all.③ opens a new query. Paste the copied script into the new query page. and modify the d
Tags: SQL Server tables, views, indexesFirst, the table related1. Create
Use[test] Go setansi_nullson go setquoted_identifieron go CREATETABLE[dbo] . [Ceshi] ([Id][int]notnull, [Name][varchar (]null), CONSTRAINT[PK_Ceshi] primarykeyclustered ([Id]asc) with (Pad_index=off,statistics_ norecompute=off,ignore_dup_key=off,allow_row_locks=on, Allow_page_lo
Displays the column information of all tables in SQL SERVER.I am working on a program for importing an Excel file from the SQL server database. I want to read the column information in the database, find a lot of information from the Internet, and finally summarize the ideal SQL
Label:1, longitudinal table turn horizontal table TABLE_A structure of the longitudinal table: Post-conversion structure: SQL example of a vertical table to a table: SELECT Name,SUM (case when Course = N ' language ' then GradeELSE 0END) as Chinese,SUM (case when Course = N ' math ' then GradeELSE 0END) as Mathematics,SUM (case when Course = N ' English ' then GradeELSE 0END) as 中文版FROM dbo. Table_aGROUP by Name 2, horizontal table to the longitudin
The pivot and Unpivot relational operators are the new features provided by SQL Server 2005, so when you use pivot and UNPIVOT for databases that are upgraded to SQL Server 2005, the compatibility level of the database must be set to 90 (you can use sp_ Dbcmptlevel stored procedures to set the compatibility level.
Usi
The pivot and Unpivot relational operators are the new features provided by SQL Server 2005, so when you use pivot and UNPIVOT for databases that are upgraded to SQL Server 2005, the compatibility level of the database must be set to 90 (you can use sp_ Dbcmptlevel stored procedures to set the compatibility level.
Usin
Label:Find the table name or the name of the stored procedure in the system table before processing it with a cursor Ps:sqlserver 2000 is using the system table is sysobjects, the Type field is: xtype; The system table for SQL Server 2005 or later is sys.objects, and the Type field is type In this article, for example, Sql2005, Sql2000 version, please follow the above instructions to replace Note the value
After creating a table on SQL Server, you often need to change the design of the table. If you have any questions, the system will prompt "you are not allowed to save the changes. You must delete the changes and recreate the following tables. The reason is that, to prevent data tables from being changed at will, the st
1. Export Excel tables from the databaseDefine a GridView1protected void Button1_Click (object sender, EventArgs e)//pour out data{if (GridView1.Rows.Count = = 0){Return}Else{gridview1.allowpaging = false;//data paging is canceled before all data can be exportedBind ();Export ("Application/ms-excel", "Filename.xls", GridView1);gridview1.allowpaging = true;//data is bound before rebindBind ();}public void Export (string fileType, String fileName, GridV
--Create a test library first test_tmp
--Create two tables based on an existing table
SELECT TOP to test_tab1 from Mistest_09428.dbo.squarer;
SELECT TOP to test_tab2 from Mistest_09428.dbo.squarer;
--Create a role
EXEC sp_addrole ' Test_role1 ';
11.
12.--Assign the permissions of select to the role test_role1GRANT SELECT on TEST_TAB1 to Test_role1;GRANT SELECT on test_tab2 to Test_role1;
, Ss.sum_total_logi Cal_writes from (SELECT s.plan_handle, SUM (s.execution_count) Sum_execution_count, SUM (s.total_elapsed_time) sum_total_elapsed_time, sum (s.total_worker_time) Sum_total_worker _time, sum (s.total_logical_reads) sum_total_logical_reads, sum (s.total_logical_w Rites) sum_total_logical_writes from Sys.dm_exec_query_stats s GROUP by S.plan_handle) As SS Cross APPLY sys.dm_exec_sql_text (ss.plan_handle) T ORDER by Sum_total_logical_reads DESC
Label:2010-01-26 sp_spaceused can view the space occupied by a table, but cannot view all the tables at once. Today, we studied this SP and wrote the following query: --Refresh system dataDBCC UPDATEUSAGE (0) with NO_INFOMSGS --8k per pageSelect name = Name,id,rows = Convert (char (one), rows),reserved = LTrim (str (reservedpages * 8.,15,0) + ' KB '),data = LTrim (str (pages * 8.,15,0) + ' KB '),index_size = LTrim (str ((usedpages-pages) * 8.,15,0) +
--- Obtain the occupied capacity of tables in the SQL Server database
Create procedure get_tableinfo
If not exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [tablespaceinfo] ') and OBJECTPROPERTY (id, n' isusertable') = 1)
Create table tablespaceinfo -- create a result storage table
(Nameinfo varchar (50 ),
Rowsinfo int, reserved varchar (20
In the past, SQL Server only performed operations on the graphic interface. Now I find that my SQL language skills are getting worse and worse. For example, how to add associations for two tables, cascade update and delete.
I checked it at night and found that two methods can be used.
Trigger method:Create trigger trg
Tags: arc sql ima LTE div database pre hone alterCreate DATABASE test
go use
test
go
CREATE TABLE UserInfo
(
Id INT IDENTITY PRIMARY KEY,
Name VARCHAR,
Age INT,
Sex BIT,
Address VARCHAR (+)
)
GO
ALTER TABLE dbo. UserInfo ADD Phone VARCHAR (one)
ALTER TABLE dbo. UserInfo ADD CONSTRAINT Age_c CHECK (age>0 and age SQL SERVER build libraries,
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.