Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to get all the tables and data in batch from SQLServer database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to get all the tables and data in batch from SQLServer database".

1. Create a database

CREATEDATABASEDB001-- default path and size

2. Create a user

CREATELOGINUSER1WITHPASSWORD='USER_PW'

3. Modify the owner of the data

USEDB001execsp_changedbowner'USER1'

4. Set READ_COMMITTED_SNAPSHOT

ALTERDATABASE[DB001] SETREAD_COMMITTED_SNAPSHOTON

5. Get all database names

SelectNameFROMMaster..SysDatabases

How to operate in batches when SQLServer database acquires all tables and data

6. Get all table names

Selectnamefromsysobjectswheretype='U'/*XType='U': represents all user tables; XType='S': represents all system tables; * /

7. Get all field names

SelectNamefromSysColumnsWhereid=Object_Id ('TableName')

8. View views, stored procedures, and functions related to a table

Selecta.*fromsysobjectsa,syscommentsbwherea.id=b.idandb.textlike'%TableName%'

9. Query the fields and data types of a table

Selectcolumn_name,data_typefrominformation_schema.columnswheretable_name=N'TableName'

10. Get the database file path

Selectltrim (rtrim (filename)) fromdbname..sysfileswherecharindex ('MDF',filename) > 0orselectltrim (rtrim (filename)) fromdbname..sysfileswherecharindex (' LDF',filename).

At this point, I believe you have a deeper understanding of "SQLServer database how to get all the tables and data in batches". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.