, Len (@PrintText)-2) WHERE right (@PrintText, 2) in
(char) +char (Ten), Char (+char (13)) SELECT @PrintText =left (@PrintText, LEN (@PrintText)-1) WHERE Right (@PrintText, 1) in (char, char ())--select @Print Text=right (@PrintText, LEN (@PrintText)-1) WHERE left (@PrintText, 1) in (char, char ()) PRINT @PrintText--p RINT '/*** len= ' + cast (len (@PrintText) as varchar) + ' Datalength= ' + cast (datalength (@PrintText) as varchar) + ' ***/' END select @[emailprotected] Select @PrintT Ext=
In the process of maintaining SQL Server database, do you often encounter thousands of similar log20050901 log table, each table data is not many, one open to see very inconvenient, or sometimes we need to summarize the data in these tables, A single open operation is also very troublesome. The following describes an automated method for merging
) + 'id from # Tmp )'?????? Exec Sp_ExecuteSql @ Str, n'@ WokNo Varchar (500) output', @ WokNo OutPut?????? Select @ WokNo, @ I -- display the employee ID in one row?????? Set @ I = @ I + 1??? End
TemporaryTableYesCreateLocal and globalTemporaryTable. LocalTemporaryThe table is only visible in the current session; GlobalTemporaryThe table is visible in all sessions.LocalTemporaryThe table name is preceded by a number character (# table_name), while the globalTemporaryThe table name is preceded b
Data dictionary is a good thing, for development, maintenance is very important.However, it is not convenient to write descriptive narration in SQL Server, how to simplify and add changes to the extended attributes in batches?It would be nice to add 2 tables and 5 stored procedures, 2 triggers, and a table-valued function.Run the following
Recently do data transfer, due to misoperation, in the system table master tables created n multiple tables is really impossible to deleteFind the following ways to share---Specify the tables in the database to be deleted use mastergodeclare @sql varchar (8000), @TableName varchar () BEGIN DECLARE cur cursor for Sele
change the resource status outside the function scope, which is also called side effect in SQL Server ). However, it is not recommended to use a large temporary result set in a function, because placing such a function in a query may cause obvious performance problems, therefore, Batch scripts such as stored procedures are generally used in this case.
The reason why dynamic scripts do not support table v
FOREIGN KEY constraint when creating a local or global temporary table. If you specify the FOREIGN KEY constraint in a temporary table, the statement returns a warning message stating that the constraint has been ignored, that the table will still be created, but does not have a FOREIGN KEY constraint. Temporary tables cannot be referenced in the FOREIGN KEY constraint.Consider using table variables without using temporary
: increments of several numbers at a time wildcard characters:_: There is only one character% any of any charactersLike ' (used to modify)Having said so much, we should pay attention to some common problems;01. Foreign Key control is not good02. In SQL Server, string and date type data are enclosed in single quotes, and numeric types are not03. Each table must have a primary key, the table without the prima
informationCreate Table table (A1 varchar), A2 char (2))--Add descriptive information to the tableEXECUTE sp_addextendedproperty n ' ms_description ', ' personnel information table ', n ' user ', n ' dbo ', n ' table ', N ' table ', NULL, NULL--Add descriptive information for field A1EXECUTE sp_addextendedproperty n ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' table ', n ' column ', n ' A1 '--Add descriptive information for fi
Tags: style ext color width int setSQL Servercursors are generally used to traverse tables, andSQL Server can easily loop through cursors to implement SQL Server traversal records in tables. However, cursors are not recommended for actual development. We know that you can al
SQL Server database's experience in operating hundreds of millions of tables is summarized. sorting hundreds of millions of tables or joining hundreds of millions of tables may cause the system to lose response.
◆ 1. I did make a large query. the involved data table conta
A Left JOINIntfsimsnew. SYSOBJECTS B onA.name = B.nameWHEREISNULL (B.name,"') ="' andA.xtype =' U 'ORDER by 1,2--Compare the differences of each table field in two databasesSELECTTable name A = Case whenISNULL (A.tablename,"') "' ThenA.tablenameELSEB.tablenameEND, field name A = a.fieldname, field name B = b.fieldname, order = a.fieldsno, Description = Case whenA.fieldtype Then ' type: '+ A.fieldtype +' --'+ B.fieldtype whenA.fieldsno Then ' Order: '+ STR (A.FIELDSNO) +' --'+ STR (B.FIE
SQL Server 2008 synchronous replication creates new tables/functions/stored procedures (without reinitialization of snapshots)One, in the production environment already has the transaction replication (the replication type is the transactional publication), needs the existing published database to add the table, the view, the stored procedure and so on, these cha
Tag: SQL Server CTO self-increment title NotepadOne, SQL Server table data copied to Excel1, new query, with SQL statements to read out the table data2, then, select the data, right-click, copy (also can be clicked together with the title copy), copied into Notepad (otherwis
Label:1.oracle querying the number of tables in the database:Select COUNT (*) from User_tablesThe premise of this operation is that the login is successful2.mysql querying the number of tables in the database:SELECT COUNT (*) TABLES, table_schema from INFORMATION_SCHEMA. TABLES WHERE table_schema = ' database ' GROUP b
Label: If table failure is deleted because of a foreign KEY constraint, all constraints are removed first:--/1th Step ********** Remove foreign KEY constraints for all tables *************************/
DECLAREC1cursor for
Select 'ALTER TABLE ['+ object_name(parent_obj)+ '] Drop constraint ['+Name+']; '
fromsysobjectswhereXtype= 'F'
OpenC1Declare @c1 varchar(8000)
Fetch Next fromC1 into @c1
while(@ @fetch_status=0)
begin
exec(@c1)
Fetch Next from
:.. From B in
"server.mappath (". ") "\data.mdb" "where."
24. Create a Database
CREATE DATABASE Database-name
25. Note: Delete database
Drop Database dbname
26. Note: Back up SQL Server
---Create
Device of Backup data
Use master
EXEC sp_addumpdevice disk, Testback,
C:\mssql7backup\MyNwind_1.dat
---start Backup
BACKUP DATABASE pubs to Testback
27. Note: Create a new table
CREATE TABLE TabNam
table because the heap Table Page is disordered and must be allocated from the iam page each time, the clustered index table can be searched through a two-way linked list.Pages allocated/sec is the number of pages allocated per second in all databases of the SQL server instance. These pages include the pages allocated from the hybrid zone and unified zone.
For queries, clustered indexes are of course the
with a table without a primary key or unique key? If your table does not has a unique index (or a PRIMARY KEY constraint or UNIQUE key constraint, both of which create a U Nique index behind the scenes), then your dynamic cursor is converted to a static cursor. And all static cursors must is read only. If one of the tables referenced by the cursor has no unique index, the cursor would be converted to STATIC. And STATIC cursors is read-only. See Using
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.